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:C29 6,"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).
=COUNTIF(CMA_Report!C1:C29 6,AND("Joh n Doe","Joe Harris")
=COUNTIF(CMA_Report!C1:C29 6,AND("Joh n Doe","Joe Harris"))
ASKER
what if it's in a different column all together?
Was missing an ) at the end...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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="Joh n Doe")*(Sheet1!D1:D296="Joe Harris")*(CMA_Report!E1:E2 96="Test") )
jppinto
=SUMPRODUCT((CMA_Report!C1
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...
ASKER
got what I needed. thanks!