Understanding How Counting Uniques Works

**nothingisthis** · 04-02-2010, 01:06 PM

Hi there -

Can someone please explain how the following formula works? Specifically the &"" part:

SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20&""))

Thanks! It has been driving me nuts!!

-Victoria

**dominicb** · 04-02-2010, 01:20 PM

Good afternoon nothingisthis

Originally Posted by nothingisthis

Can someone please explain how the following formula works?
SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20&""))

This is one of those instances where a formula works as a byproduct of how a function works in the background. The SUMPRODUCT bit can also be replaced by SUM and done as an array formula.

When a COUNTIF function has the criteria the same as the range, then each item in the range is used each time, in turn. The "1/" bit simply divides the result of the items by 1, so 5 would become 1/5 (ie a fifth), and each of these is added together.

Now consider this example :
A range contains the following numbers (only two uniques) : 5, 7, 7, 7, 5, 5, 7
There are 3 fives and four sevens. The resulting sum would add together 3 x 1/3 (for the fives - which = 1) and 4 x 1/4 (for the sevens = 1) and add the two together (=2 uniques).

The &"" bit isn't usually needed - if you have a range containing blanks then it stops errors occuring.

HTH

DominicB

**nothingisthis** · 04-02-2010, 01:47 PM

Hi Dominic -

That is mostly clear, but why then does a COUNTIF(C9:19,C9:19) result in 2, in the attached?

**martindwilson** · 04-02-2010, 01:53 PM

it is really only doing
COUNTIF(C9:19,C9) it ignores the rest of the cells c10:c19

**nothingisthis** · 04-02-2010, 01:54 PM

"When a COUNTIF function has the criteria the same as the range, then each item in the range is used each time, in turn. The "1/" bit simply divides the result of the items by 1, so 5 would become 1/5 (ie a fifth), and each of these is added together."

^This part isn't clear either. I understand that 1/ divides the results by 1, but then with your example, I'm imagining: 1/5 + 1/7 + 1/7 + 1/7 + 1/5 + 1/5 + 1/7. This doesn't equal to 2.

I think I'm not understanding how the COUNTIF really works here...

**nothingisthis** · 04-02-2010, 01:57 PM

Martin, if that's the case, then we're left with a 2.

If we apply the formula theres: 1/2, then SUMPRODUCT(1/2).

I think I'm not understanding a step.

**shg** · 04-02-2010, 01:58 PM

It's returning the first element of an array formula.

With the formula entered in E9 as it already is, select E9:E19. Then put the cursor in the formula bar, press and hold the Ctrl and Shift keys, and press Enter. You'll see the rest of the results.

To finish mimicing the single formula, in another cell enter =SUMPRODUCT(1/E9:E19)