# Understanding How Counting Uniques Works

1. ## Understanding How Counting Uniques Works

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  Register To Reply

2. ## Re: Understanding How Counting Uniques Works

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  Register To Reply

3. ## Re: Understanding How Counting Uniques Works

Hi Dominic -

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

4. ## Re: Understanding How Counting Uniques Works

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

5. ## Re: Understanding How Counting Uniques Works

"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...  Register To Reply

6. ## Re: Understanding How Counting Uniques Works

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.  Register To Reply

7. ## Re: Understanding How Counting Uniques Works

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)  Register To Reply