# COUNTIF or SUMPRODUCT counting multiple criteria

1. ## 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.

2. ## RE: COUNTIF or SUMPRODUCT counting multiple criteria

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.
>

3. ## RE: COUNTIF or SUMPRODUCT counting multiple criteria

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.
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1