Countif with multiple criteria

[Guest]

Hi, I need to count how many people meet criteria in two different columns.
See below:

Prod Mktg NX
Finance EX
Ops-Cons Svc NX
Prod Mktg EX
Finance NX

How many people in Finance are also EX?
thanks for the help!!!

 

[Guest]

Hi

Assuming your data is in col A and B try

=COUNT(IF($A$1:$A$5=A7,IF($B$1:$B$5=B7,COUNTA($B$1:$B$5))))

where A7 = the first criteria and B7 is the second criteria
the formula is an array formula so to use your press ctrl+shift+enter to use
rather than just enter.

If you have Excel 2007 you can use the easier formula

=COUNTIFS($A$1:$A$5,A7,$B$1:$B$5,B7)


--


_______________________
Naz,
London

 

[Guest]

Thank you!!! Darn...I wish I had 2007!

 

[PCLIVE]

One way:

=SUMPRODUCT(--(A1:A5="Finance"),--(B1:B5="EX"))

HTH,
Paul

 

[Bob Phillips]

Don't need 207

=SUMPRODUCT(--($A$1:$A$5="Finance"),--($B$1:$B$5="EX"))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sai Krishna]

Very very very useful. Thanks a ton

Don't need 207

=SUMPRODUCT(--($A$1:$A$5="Finance"),--($B$1:$B$5="EX"))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mmmbl]

Just out curiousity, what are the double minus signs for in the formula?

 

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html