Hi Airen,
This is the beauty of SumProduct function.
Below is the explanation:-
If you evaluate the formula in parts, you will get following look:
=SUMPRODUCT(({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})/{7;4;4;6;7;7;4;4;6;2;4;1;7;4;4;6;7;7;4;4;6;1;7;4;4;6;2;4;6})
To obtain above look, first select (B2:B30<>"") in formula and press
F9 and then select COUNTIF(B2:B30,B2:B30&"") in formula and press F9.
Now, if you see the first part of the formula, it is checking if any
value between B2 to B30 is blank or not. After this, the second part
of formula is checking the repeat counts of values with in B2 to B30 (
I am assuming you know the working of countif function).
If we include both of above functions in sumproduct, then Sumproduct
picks up the values which are not blank and Not repeated values as a
combination.
I hope this clarifies to some extent. :)
Best Regards,
DILIPandey
On 7/4/11, airen <airen1...@gmail.com> wrote:
> Hi,
> i want to count unique values in a selection. I got this formula on
> net =SUMPRODUCT((B2:B30<>"")/COUNTIF(B2:B30,B2:B30&"")) but i dont
> know how it works. So please help me understand this formula.
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Thanks & Regards,
DILIP KUMAR PANDEY, mvp
MBA,B.Com(Hons),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 62, India
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
