[SOLVED] Counting unique numeric values

I find this tip very valuable, but can someone please explain what the different functions are doing in the overall construct of the two solutions noted? I understand that the range is A2:A13.

= SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))
= SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

Thanks.


K

This is a great tip. But the formula shown under Solution 1 using a Sum/If/Frequency combination is not the same as the formula in the example which uses a Sun/N/Frequency combination. They both produce the same result. However, you should be consistant or show both as alternate solutions.

I hope I make sense when I way this....I have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?? Any advise would be greatly appreciated.

SAMPLE:

3/1 3/2 3/3 3/4 3/5 3/6 3/4
9 8 5.5 6 7 8 9


I want to capture all of the dates but I only want the total number for dates 3/1-3/4.

Short and Sweet, straight to the point. I solved my issue!! Thanks

Problem:

Counting the number of different numeric values in List1, disregarding blank cells.
Solution:

Using the FREQUENCY function as follows:
=SUM(N(FREQUENCY(A2:A12,A2:A12)>0))
Or this SUMPRODUCT formula:
=SUMPRODUCT((A2:A12


List1
4
1
2
1
6
4
4
6
5
5
3

Number of unique values 6

I find this tip very valuable, but can someone please explain what the different functions are doing in the overall construct of the two solutions noted? I understand that the range is A2:A13.

= SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))
= SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

Thanks.


K

Hi K,

It's a nasty one isn't it?!

Basically it's an array formula, so it doesn't apply to one range once, but instead it applies to all cells in the array and works out the number of uniques from there.

Breaking it down:

=COUNTIF([range],[criteria])
- this formula has two variables, [range] and [criteria], which is the range you're analysing and the criteria you're finding within that range. The result is the number of times Excel finds the criteria value within the range.

For example: =COUNTIF(A2:A13,1) would tell you that there were X number of 1s in the range A2:A13.

=SUMPRODUCT(X)
- this is one of the best functions in Excel, quite honestly. You can use it as a SUMIF, but use multiple conditions, whereas SUMIF only allows you to give one condition.
- anyway, in this context, it's rather different. If you take the formula and apply it only to one cell (as opposed to the A2:A13 range), you get:

=SUMPRODUCT((A2<>"")/COUNTIF(A2,A2&""))

which means "find the number of times A2 occurs in cell A2 [once] and divide the number of cells in the range A2 [one] by that number", i.e. 1 divided by 1 = 1. This will happen if you do the formula for one cell.

Ok, so where were we:

The full formula reads:

=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

which literally says "look at the range A2:A13 and find each occurrence of each value and divide by the number of occurrences, then add each single occurrence together to get the number of unique values".

I hope that clears it up a little bit!!

Dave