Countif based on 2 criteria

N

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
 
K

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 :)
------------------------------­------------------------------­----------------
 
P

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
 
C

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

N E Body

Thanks Ken

That did the trick!

Regards
Kenny

Ken Wright said:
=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 :)
------------------------------­------------------------------­------------ ----
Click to expand...
 
A

Ashish Mathur

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
 
Top