Countif with multiple criteria
 

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!!!
--
Paige

Countif with multiple criteria
 

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


"Paige" wrote:

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!!!
--
Paige

Countif with multiple criteria
 

Thank you!!! Darn...I wish I had 2007! :)
--
Paige


"Naz" wrote:

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


"Paige" wrote:

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!!!
--
Paige

Countif with multiple criteria
 

One way:

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

HTH,
Paul

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

Countif with multiple criteria
 

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)



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

Countif with multiple criteria
 

Very very very useful. Thanks a ton


"Bob Phillips" wrote:

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)



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

Countif with multiple criteria
 

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

"PCLIVE" wrote:

One way:

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

HTH,
Paul

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

Countif with multiple criteria
 

=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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

mmmbl wrote:

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

"PCLIVE" wrote:

One way:

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

HTH,
Paul

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




--

Dave Peterson