How to Count With Multiple Criteria

[CatFund]

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

Please help

 

[Eric W]

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.

 

[sunny102]

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)

 

[CatFund]

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

 

[Eric W]

Glad we could help!

Happy Holidays!