32
10
I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no function =COUNTDISTINCT(A2:A100) so what can I do instead? (Excel 2003)
I can't exactly use answers to this similar question because I don't want to modify the table or the filtering. I need an addition in the worksheet, not a modification.
Torben Gundtofte-Bruun
Posted 2010-09-17T14:13:03.627
Reputation: 16 308
So much googling, and finally found it. Thank you! – russds – 2015-12-11T22:42:02.940
4How/why does this work??? – RBarryYoung – 2013-04-25T20:10:51.633
4@RBarryYoung, SUMPRODUCT works like an Array function without being one. While it can do much more than what it's being used for here, here it is just checking every instance of a column, and coming up with a scaled value for each distinct value based on how many iterations there are in the column. So if there were two instances of 'a', it would add 1/2 and 1/2 to get 1. So for every distinct value, you'll add one to the sum. – Lance Roberts – 2013-04-25T20:30:48.300
1This is the part that I'm having trouble with: "coming up with a scaled value for each distinct value based on how many iterations there are in the column." If I understand this, then SUMPRODUCT is effectively executing a loop like
for each [i] in A2:A100 DO:{ sum += (([i]<>"")/COUNTIF(A2:A100,[i])) }, right? I am having a little trouble understanding how it knows to enumerate both the conditional and the 2nd COUNTIF parameter, but *not* the 1st COUNTIF parameter? Is this DWIM-magic documented/explained anywhere by MS? It would be nice to know how to write these intentionally. – RBarryYoung – 2013-04-25T21:41:14.5571Yes, you got it, it iterates an array for what would normally be a single value, multiplying all of its parameters (which in this case is just 1), then adding the iterations. SUMPRODUCTs can get very complex, but they are one of the most useful functions in Excel, it can do literal miracles. – Lance Roberts – 2013-04-25T21:47:10.907
4
This fails if the input contains blank entries. Easy fix as demoed on youtube is this:
– cfi – 2013-07-04T09:19:14.517=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). It ensures that theCOUNTIFnever returns a 0 for blank lines, which would result in aDIV/0error.1
%? The percent character does not work in Excel 2010 for appending text. It is the ampersand&that does the trick. – cfi – 2013-07-08T07:48:48.720@cfi, thanks, five-fingered that, fixed now. – Lance Roberts – 2013-07-08T08:55:12.310
Does not work properly with columns containing timestamps. It works fine with just dates tho. – Stefan – 2014-01-13T16:39:42.670