# 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).
###### Who is Participating?

Commented:
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

Commented:
=COUNTIF(CMA_Report!C1:C296,AND("John Doe","Joe Harris")
0

Commented:
=COUNTIF(CMA_Report!C1:C296,AND("John Doe","Joe Harris"))
0

Author Commented:
what if it's in a different column all together?
0

Commented:
Was missing an ) at the end...
0

Commented:
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

Commented:
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

Commented:
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

Commented:
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

Author 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.