Unique Customers

[jlkirk]

I posted this also on Woody's.

See attachment. In column A i have the names of customers separated by contract types. Some customers are listed in more than one contract type. For example, column A, rows 1-35 contain the names of customers having contract type I. In rows 36-78, the names of customers having contract type II, some of these latter customers also appear in rows 1-35. Finally, in rows 79-140, the names of customers having contract type III, again, some of these may also appear (more than once) in rows 1-78. In columns B-M, rows 1-140 are the monthly quantity of a commodity purchases by the respective sustomer under each contract type.

Ehat I would like is in row 142, columns B-M is list the number of "unique" (i. e., no duplicates) customers purchasing under contract type I. In row 143, columns B-M list the number of "unique" (i. e., no duplicates) customers purchasing under contract type II, and the same for contract type II in the same columns in row 144. Finally, in row 145, columns B-M list the number of truly unique customers doing business each month regardless of contract type. For example, if a customer in a given month purchased goods under contract types I and II, he would only be counted once.

Any ideas?

Thanks in advance

[HansV]

Your sample workbook does not match your description.

The following formulas are all array formulas, confirmed with Ctrl+Shift+Enter.

In C52: =SUM(IF(($B$2:$B$50="I")*(C$2:C$50>0),1/COUNTIFS($A$2:$A$50,$A$2:$A$50,$B$2:$B$50,"I",C$2:C$50,">0"),0))

In C53: =SUM(IF(($B$2:$B$50="II")*(C$2:C$50>0),1/COUNTIFS($A$2:$A$50,$A$2:$A$50,$B$2:$B$50,"II",C$2:C$50,">0"),0))

In C54: =SUM(IF(($B$2:$B$50="III")*(C$2:C$50>0),1/COUNTIFS($A$2:$A$50,$A$2:$A$50,$B$2:$B$50,"III",C$2:C$50,">0"),0))

In C55: =SUM(IF(C$2:C$50>0,1/COUNTIFS($A$2:$A$50,$A$2:$A$50,C$2:C$50,">0"),0))

Select C52:C55, then fill right to column N.

[jlkirk]

; sorry for the misdescription.Thanks Hans, it works great-

[HansV]

Great! Could you mention in your thread in the Windows Secrets Lounge that the problem has been solved? Thanks.

[jlkirk]

Hi Hans,
Would you mind explaining the solution?
Thanks,
Jeff

[HansV]

I'll try to do so a bit later today.

[HansV]

Let's take a slightly simpler example, with only one condition instead of two.

S0006.png

The array formula in F12 calculates the number of unique customers with classification I:

=SUM(IF($F$2:$F$9=E12,1/COUNTIFS($E$2:$E$9,$E$2:$E$9,$F$2:$F$9,E12)))

An important feature is that the second argument of COUNTIFS is not a single value but a range $E$2:$E$9, i.e. an array of values. Excel calculates COUNTIFS($E$2:$E$9,$E$2,$F$2:$F$9,E12) = 3, COUNTIFS($E$2:$E$9,$E$3,$F$2:$F$9,E12) = 1, etc., up to COUNTIFS($E$2:$E$9,$E$9,$F$2:$F$9,E12) = 3.

COUNTIFS($E$2:$E$9,$E$2:$E$9,$F$2:$F$9,E12) evaluates to {3;1;1;1;1;1;3;3}

1/COUNTIFS($E$2:$E$9,$E$2:$E$9,$F$2:$F$9,E12) evaluates to {0.333333333333333;1;1;1;1;1;0.333333333333333;0.333333333333333}

The IF statement copies the values from this array where the value in column F equals E12, and returns FALSE for the others:
{0.333333333333333;FALSE;1;FALSE;FALSE;1;0.333333333333333;0.333333333333333}

Each of the three rows for customer A with classification I contributes 1/3. This sums to 1, so that A counts only once, instead of three times.
The single row for customer B with classification I contributes 1, as does that for customer C.
The rows with classification II don't contribute to the sum since SUM ignores TRUE/FALSE values.
So the sum is 3, the number of unique customers for classification I.

[Reimer]

Hi

I have a user with a similar request, but not as complex. He wants to have a formula return the count of unique text entries in a range.
How hard is that to develop (I tried but am NOT good with array formulas)?

sample: (would return 3)
Bill
Ted
Bill
Sam


Chuck

[HansV]

As long as there are no blanks in the range, you can use the array formula (confirmed with Ctrl+Shift+Enter):

=SUM(1/COUNTIF(range,range))

For example if the entries are in A1:A50:

=SUM(1/COUNTIF(A1:A50,A1:A50))

If the range may contain blank cells, use this (still confirmed with Ctrl+Shift+Enter):

=SUM(IF(A1:A50<>"",1/COUNTIF(A1:A50,A1:A50)))

[Reimer]

Hans,

Thank you. I passed it on to the user.