Countif based on 2 criteria

[N E Body]

Hi everyone

I can use Countif to count the number of "Pass" in column AV however I only
want to count "Pass" in column AV if Am (same row) = "High"
I tried =Countif(AM:AM,"High")AND,Countif(AV:AV,"Pass") but that did not
work!

Any suggestions

Kenny

 

[Ken Wright]

=SUMPRODUCT(--(AM1:AM1000="High"),--(AV1:AV1000="Pass"))

Note, you cannot use full column references with SUMPRODUCT, so adjust
ranges to suit.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Paul B]

Kenny, here is one way,
=SUMPRODUCT((AM1:AM25="High")*(AV1:AW25="Pass"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[CLR]

One way would be to CONCATENATE the two columns together into a helper
column and then do a simple COUNTIF on that helper column for the
combination desired..........

Vaya con Dios,
Chuck, CABGx3

 

[N E Body]

Thanks Ken

That did the trick!

Regards
Kenny

=SUMPRODUCT(--(AM1:AM1000="High"),--(AV1:AV1000="Pass"))

Note, you cannot use full column references with SUMPRODUCT, so adjust
ranges to suit.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­------------ ----
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­------------ ----

 

[Guest]

Hi,

Another way to do it is through array formulas (Ctrl+Shift+Enter)

SUM(IF((AM6:AM8="Pass")*(AV6:AV8="High"),1,0))

Regards,

Ashish