Countif based on 2 criteria
 

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

=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 :-)
------------------------------*------------------------------*----------------


"N E Body" wrote in message
...
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

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

"N E Body" wrote in message
...
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

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" wrote in message
...
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

Thanks Ken

That did the trick!

Regards
Kenny

"Ken Wright" wrote in message
...
=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 :-)
------------------------------*------------------------------*------------
----


"N E Body" wrote in message
...
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

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

"N E Body" wrote:

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