Returning only unique numbers using COUNTIFS, can it be done?

[Puck101]

Hi

I have a table of data that I am using countifs to return the number of accounts that meet my criteria, the problem I have is within the data I have multiple rows of the same account so they are being double counted. Is there away around this?

In my data I have Account number, customer name, date, product, sales person, revenue and I'm trying to make a summary so i'm using countifs to count the accounts where a date falls with a certain month, the sales rep matches the summary name and the revenue is greater than £299 but how do I get it to count only unique account numbers?

 

[ndsutherland]

You could also use

=SUM(IF(FREQUENCY(IF(E2:E41=J2,IF(SUMIFS(F2:F41,B2:B41,B2:B41,C2:C41,">="&L2,C2:C41,"<"&M2)>K2,IF(C2:C41>=L2,IF(C2:C41<M2,MATCH(A2:A41,A2:A41,0))))),ROW(A2:A41)-ROW(A2)+1),1))

Confirmed with CTRL+SHIFT+ENTER

Where

J2 = Sales Rep Name
L2 = Earliest Date
M2 = Day After Latest Date
K2 = Revenue Threshold
Column B contains Customer Names

This would eliminate the need for a revenue sum helper column