Using COUNTIF with multiple conditional criteria and only counting once when a specific value is Unique in another column

Apologies, not sure I've titled this well

Hi I wonder if some one can suggest a solution to this problem I want to count the number of unique customers (only count each unique customer once) that match on 2 or more criteria's, my jumping of points are as follows:

=SUM(IF($B$7:$B$5904<>"",IF($R$7:$R$5904<=6,1/(COUNTIFS($R$7:$R$5904,"<="&6,$B$7:$B$5904,$B$7:$B$5904))),0))

=SUMPRODUCT(1*(FREQUENCY(IF(R7:R5904<=6,MATCH(B7:B5904,B7:B5904,0)),ROW(R7:R5904)-ROW(R7))>0))

Both work to a point but I need to be able to count when a value falls between a range of 7 to 14 and also matches a specific criteria in column D (only count when value in this column matches "FD").

I've been trying to figure this out how to write this for a while and for some reason just can get my head round it, thanking anyone who can help with this in advance.

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

AnnaThomasH

Independent Advisor

Hi Richard I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself. You can use an array formula to count the number of unique customers that match multiple criteria. Here’s an example formula that counts the number of unique customers in column B where the value in column R is between 7 and 14 and the value in column D is “FD”: =SUM(--(FREQUENCY(IF((R7:R5904>=7)*(R7:R5904<=14)*(D7:D5904="FD"),MATCH(B7:B5904,B7:B5904,0)),ROW(B7:B5904)-ROW(B7)+1)>0)) This is an array formula, so you’ll need to enter it by pressing Ctrl+Shift+Enter instead of just Enter. I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions. Best Regards, AnnaThomas Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Excel Top Contributors:

Using COUNTIF with multiple conditional criteria and only counting once when a specific value is Unique in another column

Replies (6)

Question Info

Using COUNTIF with multiple conditional criteria and only counting once when a specific value is Unique in another column

Replies (6) 

Question Info

Replies (6)