# Excel Formula Question [SOLVED]

I have been shown the end of my worldRound Rock, TXRegistered User regular
edited January 2008
This is about to make my head explode. Does anyone know of a way to make Excel count a row based on the condition of 2 different columns?

Specifically, I want to use COUNTIF for if E3:E5000 is Yes AND F3:F5000 is Yes, comparing Ex with Fx. I tried playing around with AND(), but it's not really helping me. Maybe I'm just missing something. Any ideas?

Sir Carcass on

## Posts

• Registered User regular
edited January 2008
This is about to make my head explode. Does anyone know of a way to make Excel count a row based on the condition of 2 different columns?

Specifically, I want to use COUNTIF for if E3:E5000 is Yes AND F3:F5000 is Yes, comparing Ex with Fx. I tried playing around with AND(), but it's not really helping me. Maybe I'm just missing something. Any ideas?

Just make a new column with the formula of =and(e3,f3)
drag that down the whole column and use a countif function on that column, testing for true.

Daenris on
• I have been shown the end of my world Round Rock, TXRegistered User regular
edited January 2008
I would like to have it all done in one cell, but that gives me something to play around with. If I can't figure anything else out, I guess I could make that column hidden.

Sir Carcass on
• Registered User regular
edited January 2008
Well, I just played with it a bit, and I don't believe you'll be able to get the countif function to work like this. The criteria for the countif function is based on a simple test with no reference to the cell number, so no easy way to even come up with an and criteria. I really think the extra column will be the easiest and quickest solution. After spending years forcing Excel to do what I want I'm all about quick and easy solutions over the more visually/mentally appealing ones

Good luck though.

Daenris on
• Registered User regular
edited January 2008
Excel 2007 has a COUNTIFS() function that does what you want, but it's not in prior versions. I think the only two ways to do this in 2003 and prior are the hidden second column you two discussed or writing your own COUNTIFS() in VBA, which is probably not worth the hassle.

blincoln on
Legacy of Kain: The Lost Worlds
http://www.thelostworlds.net/
• Registered User, ClubPA regular
edited January 2008
Yeah there's really no way to do this without using a third column.

Pheezer on
IT'S GOT ME REACHING IN MY POCKET IT'S GOT ME FORKING OVER CASH
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
• I have been shown the end of my world Round Rock, TXRegistered User regular
edited January 2008
Okay, guess I'll have to go with a hidden column. Thanks for all of the help.

Sir Carcass on
• Registered User regular
edited January 2008
Actually, this can be done in Excel versions prior to 2007, and quite easily. Just use an array formula. Based on your example, you'd stick the following formula into your 'total' cell:
=SUM(IF(E3:E5000="Yes",IF(F3:F5000="Yes",1,0),0))


...Then, instead of hitting Enter, hit Ctrl+Shift+Enter instead (otherwise Excel won't understand what you're trying to do by entering arrays instead of single-cell references).

What it does is use IF() logic to return a 1 if both cells equal "Yes", and a zero otherwise. This calculation is iterated throughout the entire array, and the total is counted up by the SUM() function.

Array formulas are pretty slick. The next time you're trying to solve a problem like this, you may want to check the Excel documentation for more info on them. You'll find that many of the cases that would seem to require some sort of 'uber-COUNTIF()' function can be done with array formulas instead.

whuppins on