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")*(Attendance!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")*(Attendance!D:D="Atlanta"))
> =COUNTIF(Attendance!C:C, {"Attorney","Atlanta"})
> =SUMPRODUCT(--(Attendance!C:C="Attorney"),--(Attendance!D:D="Atlanta"))
>
> Any suggestions? Thank you.
>

You can not use the whole column so you need to specify the range

=SUMPRODUCT(--('Attendance'!C2:C1000="Attorney"),--('Attendance'!D2:D1000="atlanta"))

a good idea would be to replace the hard coded names (attorney and atlanta)
with cell references, that way you don't have to edit the formula if the
criteria is changed

Regards,

Peo Sjoblom

"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")*(Attendance!D:D="Atlanta"))
> =COUNTIF(Attendance!C:C, {"Attorney","Atlanta"})
> =SUMPRODUCT(--(Attendance!C:C="Attorney"),--(Attendance!D:D="Atlanta"))
>
> Any suggestions? Thank you.
>