• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

the Logic of: =SUMPRODUCT((G2:G89)/COUNTIF(G2:G89;G2:G89)) and how

M

MarkH

New Member
Hi,


The above works to get me the distinct number of values from a list, but I don't understand why. Some help would be nice.


Maybe partuially to me not understanding it, I cannot "customize" this one to suit other needs. How can this be done (so I understand) using the CountIF formula and anything else you need. The logic to just hop from array and criteria to array and criteria seems appealing. If I also get why the array and why not, I am all set.


Thanks.
 
Ignoring the double quotes, and assuming formula is this:

=SUMPRODUCT((G2:G89)/COUNTIF(G2:G89;G2:G89))


formula works by dividing each value by it's count. And example...

Let's say you start with this as your values

1,6,6,6

the COUNTIF function for each respective member gives this array:

1,3,3,3

Dividing array 1 by array 2 gives

1,2,2,2


The SUMPRODUCT adds the array values up, giving 7. We see this is correct as 1+6=7.
 
Hi MarkH!


Same reservation as Montrey, am doubtful does this formula really works. I found some similar formulas for "Counting Unique Values" (Not Unique Values their selves) when i googled-out, so what this formula is giving you, count or values? BTW it doesn't seems to work normally or as an array formula either.


Regards,

Faseeh
 
Of note, this formula "does" work:

=SUMPRODUCT((G2:G89&"")/COUNTIF(G2:G89,G2:G89&""))


However, it appears to be fairly similar to this:

=SUMPRODUCT((G2:G89)/COUNTIF(G2:G89,G2:G89))


The only different is that if one of the cells is blank, the former gives a #VALUE error while the latter gives a #DIV/0 error.
 
@Luke M,


Thank you, It does work if i replace ";" with "," in COUNTIF() part, but not when i merely copy paste formula from the title rather it tells me that "Formula Contains an error". :)
 
@Luke


What does the &"" do in the function??

Also when used in a sumproduct what does the & do to the formula. I thought only a range could be used and nothing else. or like (range=value)*(range). What the hell does (range&value) do?
 
@Faseeh

Common issue is switching between comma and semicolon, as it depends on local settings as to what the divider is to be.


@Montrey

Technically, it's concatenating a zero-length text string onto all the values. Why you would do this, I'm not sure. It's certainly okay to use it within the SUMPRODUCT function. I've only see it used before when concatenating two ranges together. Say we had a column of Names and Cars. Instead of doing some combination of criteria, you could do:

=SUMPRODUCT(--(A2:A10&"-"&B2:B10="Bob-Ford"))


Rather than writing:

=SUMPRODUCT((A2:A10="Bob")*(B2:B10="Ford"))


Again, not sure why you'd want to do it this way, but you certainly are "allowed" to.
 
Whoops, my bad for putting something in w/o explaining it. The double negative is there to force XL to treat the array as numbers. The logic check gives us a bunch of True/False outputs, but the SUMPRODUCT won't add those. Our options are to either multiply the array by another array, multiply the whole thing by one, or use the double negative. The double negative works because a single negative would change this:

True,False,False,True

to this:

-1,0,0,-1


and so a double negative changes it to this:

1,0,0,1


Thus allowing the summation to work correctly.
 
@Luke


oh the double "" converts the range to text and makes it so no errors occur or some nonsense. as mentioned here

http://www.youtube.com/watch?v=XPb0S3Rbdf4


video also explains counting Unique values very well.
 
I think the formula he really found was


=SUMPRODUCT((G2:G89<>"")/COUNTIF(G2:G89,G2:G89&""))


The basic formula is just


=SUMPRODUCT(1/(COUNTIF(G2:G89,G2:G89)))


but this returns a #Div/0 error if G2:G89 is all empty, so we force a test against blank like so


=SUMPRODUCT(1/(COUNTIF(G2:G89,G2:G89&"")))


but this counts any blanks in the range G2:G89 as a unique value so it overstates it by 1. This can be overcome by testing for blank


=SUMPRODUCT((COUNTIF(G2:G89<>"")/(COUNTIF(G2:G89,G2:G89)))
 
@xld


:p I did found all these formula as well when i goggled the topic, the second one is Chandoo's himself formula!
 
No it is not, it was around long before Chandoo became prominent. You may have found a post by Chandoo, but it has evolved from the work of many people over many years.
 
As XLD mentions, this formula is a natural way to calculate unique values in a range. I did learn this from elsewhere during my work. Since I loved the formula, I shared with all on the site.
 
You must log in or register to reply here.
Back
Top