Link to home
Start Free TrialLog in
Avatar of sburgoin
sburgoin

asked on

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).
Avatar of jppinto
jppinto
Flag of Portugal image

=COUNTIF(CMA_Report!C1:C296,AND("John Doe","Joe Harris")
=COUNTIF(CMA_Report!C1:C296,AND("John Doe","Joe Harris"))
Avatar of sburgoin
sburgoin

ASKER

what if it's in a different column all together?
Was missing an ) at the end...
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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()...
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...
got what I needed.  thanks!