# How to Count With Multiple Criteria

#### CatFund

##### New Member
I need to count the number of unique customers who fit into a date range and meet a certain criteria

Dates are in A2:A40000
Customer names are in C2:c40000
Start Date is in X5
End Date is in 52
Filtering Criteria is a Yes or No in M2:M40000

I am trying to find out how many customers (there are multiple entries for the same customer so I need to only count the customer once no matter how many times their name appears) who have a N in column M.

I have tried this formula, but it returns a ridiculous result

=SUMPRODUCT(IF((A2:A40000<=Y5)*(A2:A40000>=X5),COUNTIFS(M2:M40000,"N")*1/COUNTIFS(A2:A40000,"<="&Y5,A2:A40000,">="&X5,C2:C40000,C2:C40000),0))

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to the forum.

Maybe:

=SUM(SIGN(FREQUENCY(IF((A2:A40000>=X5)*(A2:A40000<=Y5)*(M2:M40000="Yes"),MATCH(C2:C40000,C2:C40000,0)),ROW(C2:C40000)-ROW(C2)+1)))

confirmed with Control+Shift+Enter.

Try this formula, it is an array formula so make sure to CTRL+SHIFT+ENTER

SUM(IF(("No"=\$M\$2:\$M\$40000)*(\$A\$2:\$A\$40000<=Y5)*(\$A\$2:\$A\$40000>=X5), 1/COUNTIFS(\$M\$2:\$M\$40000, "No", \$C\$2:\$C\$40000, \$C\$2:\$C\$40000, \$A\$2:\$A\$40000, "<=" &Y5,\$A\$2:\$A\$40000, ">=" &X5)), 0)

Thank you so much!!! I tried the formula that Eric gave me and it worked perfect. I didn't try the one that Sunny gave me, but I will if I run into any issues. Thanks again!!!

Glad we could help!

Happy Holidays!

Replies
11
Views
561
Replies
1
Views
515
Replies
3
Views
138
Replies
15
Views
1K
Replies
9
Views
690

1,202,987
Messages
6,052,939
Members
444,617
Latest member
Rush1984

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back