COUNTIF or SUMPRODUCT counting multiple criteria

K

Kim

I want to count the number of entries that have "attorney" in column C AND
"atlanta" in column D. I only want to count the entries that meet both
criteria (not one OR the other). I have tried numerous formulas usually get
an error #NUM!

Here is what I have tried.
=SUMPRODUCT((Attendance!C:C="Attorney")*(Attendance!D:D="Atlanta"))
=COUNTIF(Attendance!C:C, {"Attorney","Atlanta"})
=SUMPRODUCT(--(Attendance!C:C="Attorney"),--(Attendance!D:D="Atlanta"))

Any suggestions? Thank you.
 
B

B. R.Ramachandran

Try this: =SUM(IF(Cb:Ce="attorney",IF(Db:De="Atlanta",1,0))), where your
data begin at Row# b and end at Row# e (substitute actual row numbers for b
and e); hit CONTROL+SHIFT+ENTER.

Better would be, =SUM(IF(Cb:Ce=$X$1,IF(Db:De=$Y$1,1,0))), where the cells
$X$1 abd $Y$1 (or any other you choose) contain the criteria, attorney and
Atlanta repsectively.

B. R. Ramachandran
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top