COUNTIF or SUMPRODUCT counting multiple criteria
 

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")*(Attendanc e!D:D="Atlanta"))
=COUNTIF(Attendance!C:C, {"Attorney","Atlanta"})
=SUMPRODUCT(--(Attendance!C:C="Attorney"),--(Attendance!D:D="Atlanta"))

Any suggestions? Thank you.

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

"Kim" wrote:

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")*(Attendanc e!D:D="Atlanta"))
=COUNTIF(Attendance!C:C, {"Attorney","Atlanta"})
=SUMPRODUCT(--(Attendance!C:C="Attorney"),--(Attendance!D:D="Atlanta"))

Any suggestions? Thank you.