Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Hope this is clear to you.

jppinto

CountUniques.xlsx

Reseller Customer Unique Count

INSIGHT 1252 Properties, Inc. 2

INSIGHT 1252 Properties, Inc. 2

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

=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.

Here are both formulas in Barry's sample workbook:

26874647.xlsx

It's my understanding that the user wants unique customers of a reseller that are not customers of other resellers, right?

jppinto

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.

=SUMPRODUCT((A$2:A$90=D3)*

26874647.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

=SUMPRODUCT((COUNTIF(B$2:B

26874647.xlsx

regards, barry

=SUM(IF(COUNTIF(B$2:B$9,B$

Like Barry's formula, it must be array-entered (CTRL + Shift + Enter).

26874647.xlsx

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

I think, though, that you could combine some elements of both for a shorter non-array version, i.e.

=SUMPRODUCT((COUNTIFS(A$2:

confirmed with CTRL+SHIFT+ENTER

That doesn't allow blanks - if you want to allow those then try

=SUMPRODUCT((COUNTIFS(A$2:

regards, barry