| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif, Multiple criteria | Excel Discussion (Misc queries) | |||
| countif multiple criteria | Excel Worksheet Functions | |||
| COUNTIF MULTIPLE CRITERIA | Excel Discussion (Misc queries) | |||
| Countif for multiple criteria | Excel Worksheet Functions | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
