Sir Carcass
I have been shown the end of my worldRound Rock, TXRegistered User regular

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?

Specifically, I want to use COUNTIF for if E3:E5000 is Yes

0

## Posts

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.

DaenrisonSir CarcassonGood luck though.

Daenrisonblincolnonhttp://www.thelostworlds.net/

PheezeronCUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH

Sir Carcassoncanbe 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:...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.

whuppinson