glabossi
asked on
Need unique count of customer name to reseller name formula ASAP!!!
I have a large file of customers and resellers and need unique count of company name for the resellers.
Reseller Customer
SAR Inc McDonalds
SAR Inc Burger King
SAR Inc Wendy's
SAR Inc Pizza Hut
PAUL DOT Macy's
PAUL DOT McDonalds
PAUL DOT Wendy's
How do I show that SAR truly has 2 unique companies and PAUL DOT has 1 Unique company.
I gave a simple example but I have 3K resellers tied to numerous companies.
Any quick help will be appreciated!!!
Reseller Customer
SAR Inc McDonalds
SAR Inc Burger King
SAR Inc Wendy's
SAR Inc Pizza Hut
PAUL DOT Macy's
PAUL DOT McDonalds
PAUL DOT Wendy's
How do I show that SAR truly has 2 unique companies and PAUL DOT has 1 Unique company.
I gave a simple example but I have 3K resellers tied to numerous companies.
Any quick help will be appreciated!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You didn't mentioned that you had duplicate on your data... let me see now.
Hello glabossi,
If you have a reseller name in D3 then you can use this formula for a count of different customers, see attached.
=SUMPRODUCT((MATCH(A$2:A$8 &"-"&B$2:B $8,A$2:A$8 &"-"&B$2:B $8,0)=ROW( A$2:A$8)-R OW(A$2)+1) *(A$2:A$8= D3))
regards, barry
If you have a reseller name in D3 then you can use this formula for a count of different customers, see attached.
=SUMPRODUCT((MATCH(A$2:A$8
regards, barry
It's not that simple Barry...he wants unique costumers that are only from that reseller! That's the problem...
In jppinto's sample workbook, you can use the formula for the count of unique customers of a reseller:
=SUMPRODUCT((A$2:A$90=F4)/ ((A$2:A$90 <>F4)+COUN TIFS(A$2:A $90,F4,B$2 :B$90,B$2: B$90)))
The reference to row 90 is arbitrary, and may extend beyond the data. The reference to cell F4 is to the reseller's name.
=SUMPRODUCT((A$2:A$90=F4)/
The reference to row 90 is arbitrary, and may extend beyond the data. The reference to cell F4 is to the reseller's name.
Barry's formula works as requested (and tolerates blank cells to boot)--but mine is shorter for a change.
Here are both formulas in Barry's sample workbook:
26874647.xlsx
Here are both formulas in Barry's sample workbook:
26874647.xlsx
I'm I the only one that understood the question or the only one that didn't?!?
It's my understanding that the user wants unique customers of a reseller that are not customers of other resellers, right?
jppinto
It's my understanding that the user wants unique customers of a reseller that are not customers of other resellers, right?
jppinto
use a countif on the customer for the same column. That will give the number of customers in that column. Then filter on the count field, and filter for the quantity of 1. This will display the reseller and unique customer.
@darcy_morse: The problem is when there are duplicate resellers+customers! If there wheren't duplicates, my fisrt solution would work!
ASKER
jppinto is correct. My original posts did not include duplicates but the formula needs to account for it.
col A Col B col C Col D
Reseller Customer = concatenate(A1,"-",B1) =countif(B:B,B1) - countif(C:C,C1)
Column D searches for number of customers and sutracts number of reseller customer combinations. From there you can look where column D = 0.
Reseller Customer = concatenate(A1,"-",B1) =countif(B:B,B1) - countif(C:C,C1)
Column D searches for number of customers and sutracts number of reseller customer combinations. From there you can look where column D = 0.
To get the counts of 2 and 1, you might use:
=SUMPRODUCT((A$2:A$90=D3)* (COUNTIF(B $2:B$90,B$ 2:B$90)=1) )
26874647.xlsx
=SUMPRODUCT((A$2:A$90=D3)*
26874647.xlsx
Sorry glabossi, I misunderstood the requirement, sounds like you understood it better jp!
Here's a formula solution
=SUM(IF(FREQUENCY(IF(COUNT IFS(A$2:A$ 10,"<>"&D2 ,B$2:B$10, B$2:B$10)= 0,IF(B$2:B $10<>"",MA TCH(B$2:B$ 10,B$2:B$1 0,0))),ROW (B$2:B$10) -ROW(B$2)+ 1),1))
That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER
where D2 contains the reseller name
see attached example
regards, barry
26874647v2.xlsx
Here's a formula solution
=SUM(IF(FREQUENCY(IF(COUNT
That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER
where D2 contains the reseller name
see attached example
regards, barry
26874647v2.xlsx
If there might be duplicates, then you get 2 and 1 with:
=SUMPRODUCT((COUNTIF(B$2:B $9,B$2:B$9 )=COUNTIFS (A$2:A$9,D 3,B$2:B$9, B$2:B$9))/ ((COUNTIF( B$2:B$9,B$ 2:B$9)<>CO UNTIFS(A$2 :A$9,D3,B$ 2:B$9,B$2: B$9))+COUN TIFS(A$2:A $9,D3,B$2: B$9,B$2:B$ 9)))
26874647.xlsx
=SUMPRODUCT((COUNTIF(B$2:B
26874647.xlsx
just to clarify, then - I think the last formula suggested by Brad will give the same results as the last formula that I suggested - my version also allows blank rows......
regards, barry
regards, barry
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Barry,
Nicely done!
Brad
Nicely done!
Brad
ASKER
Reseller Customer Unique Count
INSIGHT 1252 Properties, Inc. 2
INSIGHT 1252 Properties, Inc. 2