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?

 

[123rickfear]

is it just unique account numbers you want? or unique accounts and dates? i.e if an account ordered stock on 01/01/2015 and 02/01/2015 should it produce 1 result or 2?

 

[Puck101]

Hi, Just unique accounts. All the other criteria is just basically filtering the data for a summary so I can report on each sales person, for each month and where the sales are greater than £299, but once thats done I just want to count the number of customers.
So as per your question I'd want it to return 1. The other complication is if a customer makes to orders within the same month for say 150 each, I'd want that to be included as summed up they are >299. I can work around this by using a separate calculated column but just thought id mention it.

 

[123rickfear]

could you put =COUNTIF($A$1:A1,A1) in a column, and drag it down? A being the column where the account number is. It will put a 1 next to the first instance a number exists, a 2 for the second etc. You can then do you lookup to the 1's.

 

[Puck101]

I don't think so unless I'm mis-understanding as that doesn't work with the other elements/criteria of the COUNTIFS. For example, there may be an account that appears multiple times so doesnt have a 1 next to it but if those instances are in previous months then I don't care, I want to count it again this month.

 

[123rickfear]

ok, sorry, but earlier when I asked you said to return a 1 as the result.

 

[Puck101]

Sorry yeah I mis-understood the question, I want it on unique account numbers and months (and sales person and value). When I read your question I read the dates 1st Jan and 2nd Jan in this case I'd want it to just return me 1 however if it was 1st Jan and 1st Feb I'd want it to return 2 (count the account for both months)

 

[123rickfear]

if you add another column do A:A&text(B1,"mmmm") it will give you account number followed by month of the date. (assuming your date is in column B. You can then change the formula I gave you earlier to this new column.

 

[Puck101]

Thank you

 

[ndsutherland]

This is the unique count with criteria formula

=SUM(IF(FREQUENCY(IF(E2:E41="Fred",IF(F2:F41>300,IF(MONTH(C2:C41)=1,MATCH(A2:A41,A2:A41,0)))),ROW(A2:A41)-ROW(A2)+1),1))

confirmed with CTRL+SHIFT+ENTER (not just ENTER)

Where
-Column E contains Sales Reps
-Column C contains Dates
-Column F contains Revenue
-Column A contains Account Numbers