 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008Countif 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!!!  SignaturePaige 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) Signature_______________________ Naz, London > Hi, I need to count how many people meet criteria in two different columns. > See below: [quoted text clipped - 7 lines] > How many people in Finance are also EX? > thanks for the help!!! Thank you!!! Darn...I wish I had 2007! :) SignaturePaige > Hi > [quoted text clipped - 21 lines] > > How many people in Finance are also EX? > > thanks for the help!!! One way: =SUMPRODUCT(--(A1:A5="Finance"),--(B1:B5="EX")) HTH, Paul > Hi, I need to count how many people meet criteria in two different > columns. [quoted text clipped - 8 lines] > How many people in Finance are also EX? > thanks for the help!!! Just out curiousity, what are the double minus signs for in the formula? > One way: > [quoted text clipped - 15 lines] > > How many people in Finance are also EX? > > thanks for the help!!! =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 > Just out curiousity, what are the double minus signs for in the formula? > [quoted text clipped - 19 lines] > > > -- > > > Paige SignatureDave Peterson Don't need 207 =SUMPRODUCT(--($A$1:$A$5="Finance"),--($B$1:$B$5="EX")) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, I need to count how many people meet criteria in two different > columns. [quoted text clipped - 8 lines] > How many people in Finance are also EX? > thanks for the help!!! Very very very useful. Thanks a ton > Don't need 207 > [quoted text clipped - 12 lines] > > How many people in Finance are also EX? > > thanks for the help!!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.