Counting appearence of number just once

**5173** · 10-13-2010, 09:03 AM

Hello guys.

I hope my question would be easy for ya :-). I have a column with numbers (approx 500 numbers), which may repeat (e.g. 1, 1, 2, 3, 4, 5, 6, 6). All the numbers in the column, BUT when it appears more than once, I need to count it just once (that means that result of the example would be 6). I guess there is some way throught COUNTIF but I have no idea how.

could you help me?

Thanks in advance

**NBVC** · 10-13-2010, 09:07 AM

Try:

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

**5173** · 10-13-2010, 09:38 AM

Wow, it seems to be working. Excel just had a problem with comma in the second part of the formula. But it looks it is working with semi-colon.

Could you please just briefly explain what I am doing with the formula??

Thanks in advance.

**NBVC** · 10-13-2010, 10:02 AM

The Sumproduct() part checks for non blank entries in the range.. and assigns TRUE/FALSE based on if the cells have an entry or are blank.

The COUNTIF part counts how many of each entry there are and produces an array of numbers.

When you divide the SUMPRODUCT TRUE/FALSE array by the COUNTIF array, the TRUE/FALSEs are converted to 1/0, respectively..

1 divided by each value in the COUNTIF range produces a decimal value equivalent to a fractional portion of the repeated value... eg, if you have three 2's in your range, then the COUNTIF produces, three 3's where the 2's are positioned in the range ... the Sumproduct will produce TRUEs at those same positions.. which convert to 1's... so then 1/3 would be .33333333 (this occurs 3 times adding up to 1). When all is said and done, you will be left with a SUMPRODUCT array of fractions.. and all the fractions for a repeated part add up to 1.. so you end up with the number of unique entries.

If you use the formula Auditor with the formula on a small range (say A1:A10) then you will see the steps and it may make more sense.

**DonkeyOte** · 10-13-2010, 10:08 AM

Originally Posted by 5173

Excel just had a problem with comma in the second part of the formula. But it looks it is working with semi-colon.

If you post a sensible location in your profile you might find you get personalised solutions... the delimiters vary by locale ... specifying "Somewhere, Somewhere" is not helping yourself.

**5173** · 10-13-2010, 10:17 AM

@NBVC: Thanks a lot for precise explanation. It is much more clear now.

@DonkeyOte: You are right, I did not think of that aspect. I will correct it for the next time.

Best Regards.