» Counting Unique Numeric Values or Unique Data in a List

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

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

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:
[INDENT]=SUMPRODUCT((A2<>"")/COUNTIF(A2,A2&""))[/INDENT]
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:
[INDENT]=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))[/INDENT]
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

I'm trying to figure out how the countif formula works in this context, and I'm ready to throw myself off the roof of my building!! Can someone explain what the countif is doing? If I put it alone in a cell to see the results, it is inconsistent. Usually it returns a count of whatever value has the highest frequency in a range, but that's not always what it returns. For example, it returns "1" if you put the following numbers in cells A2:A13: 4,1,1,1,2,3,5.

=COUNTIF(A2:A13,A2:A13&"")

Why is the range re-stated in the counting criteria?
What does the &"" do??

Thank you! Remember, you're not just answering an excel question, you're saving someone from having to hose my remains off the sidewalk downstairs.