Counting the number of unique numeric values or unique data in List1, disregarding blank cells.
Solution1:
To count the number of unique values use the SUM, IF, and FREQUENCY functions as shown in the following formula:
= SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))
Solution 2:
To count the number of unique data use the SUMPRODUCT and COUNTIF functions as shown the following formula:
=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))
Screenshot // Counting Unique Numeric Values or Unique Data in a List
Can someone explain the functions?
KLN wrote on November 16, 2005 12:54 EST
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.
Explanation of functions
K wrote on December 31, 1969 19:00 EST
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.
Solution 1 formula not the same as example
Frank wrote on December 31, 1969 19:00 EST
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.
Add dates and total hours
Tamara Stephens wrote on December 31, 1969 19:00 EST
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.
Excellent Tip
Ilsa Gil wrote on December 31, 1969 19:00 EST
Short and Sweet, straight to the point. I solved my issue!! Thanks
Re: Explanation of functions
dgromanis wrote on November 21, 2006 08:46 EST
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
How does this countif work?
BlueDaze wrote on November 21, 2006 13:25 EST
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.
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Writing Excel Macros with VBA, 2nd Edition
