COUNTIF On Two Columns

Hi all,

I'm trying to create a formula that counts in two columns. Basically I have one column that has either 'Type 1' or 'Type 2' listed in it, then another that users enter dates into if/when they complete a task.

What I'm trying to do is get Excel to count at the same time a) if a column (G2:G800) has a date entered, and if so count it if it's 'Type 1'. I've fiddled around with a number of formula ideas but seem to be having no luck.

Can anyone suggest a solution?

TIA,

SamuelT

=SUMPRODUCT((A2:A8000="Type 1")+0,(G2:G8000<>"")+0)

adjust ranges to suit. Assumes you won't have anything other than dates in the second column.

I wondered what the +0 was for in your formula, so I tried it out.

What a nice trick for changing TRUE to 1 and FALSE to 0.


Mark.

Hi again,

Thanks for that. Got a slight oddity that hopefully someone might be able to suggest. So Richard's suggestion works great, however when I change the formula to:

=SUMPRODUCT((C10:C1000="Type 2")+0,(F10:F1002<>"")+0)

I get a #VALUE! returned. And also when I change it to:

=SUMPRODUCT((C2:C1001="Type 1")+0,(G10:G1001="Y")+0)

I get a #N/A return.

Any suggestions why this might be the case?

TIA,

SamuelT

First problem solved, the two ranges weren't equal...

Hi Samuel

=SUMPRODUCT((C2:C1001="Type 1")+0,(G10:G1001="Y")+0)

The two ranges here aren't equal either. I suspect the #NA results from there being this error value in one of your ranges though.