excel formula

I need to create a formula in excel that provides results only if 2 criteria are met in 2 different columns.  I curently use the following formula: =COUNTIF(CMA_Report!C1:C296,"John Doe") .   what formula can I use so that both criteria oreven 3 criteria, if met, will provide me with results (an item count on a table).
sburgoinAsked:
Who is Participating?
 
barry houdiniCommented:
Try COUNTIFS (with an "S"), e.g.

=COUNTIFS(CMA_Report!C1:C296,"John Doe",CMA_Report!D1:D296,4)

that would count rows where column C contains the name John Doe and column D contains a 4

regards, barry
0
 
jppintoCommented:
=COUNTIF(CMA_Report!C1:C296,AND("John Doe","Joe Harris")
0
 
jppintoCommented:
=COUNTIF(CMA_Report!C1:C296,AND("John Doe","Joe Harris"))
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sburgoinAuthor Commented:
what if it's in a different column all together?
0
 
jppintoCommented:
Was missing an ) at the end...
0
 
barry houdiniCommented:
I don't believe you can use an AND like that in COUNTIF jp,

sburgoin, see my suggestion above for two criteria in two columns

barry
0
 
jppintoCommented:
You can use a SUMPRODUCT() function, something like this:

=SUMPRODUCT((CMA_Report!C1:C296="John Doe")*(Sheet1!D1:D296="Joe Harris")*(CMA_Report!E1:E296="Test"))

jppinto
0
 
jppintoCommented:
Barry is right, COUNTIF can't be used with an AND...sorry, I wasn't thinking properly. But the SUMPRODUCT can be an alternative to COUNTIFS()...
0
 
jppintoCommented:
What version of Excel are you using? If it's prior to Excel 2007 you don't have COUNTIFS() function, you need to do it using SUMPRODUCT() function...
0
 
sburgoinAuthor Commented:
got what I needed.  thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.