Excel Formula Question [SOLVED]

Sir Carcass

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?

Daenris

Sir Carcass wrote: »

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.

Sir Carcass

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.

Daenris

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.

blincoln

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.

Pheezer

Yeah there's really no way to do this without using a third column.

Sir Carcass

Okay, guess I'll have to go with a hidden column. Thanks for all of the help.

whuppins

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.