Nice explanation Haseeb (HTH).. !!
Regards,
DILIPandey
On Tue, Jul 5, 2011 at 12:34 AM, Haseeb Avarakkan <
haseeb.avarak...@gmail.com> wrote:
> Hello AIren,
>
> Consider B1:B6 we have these values;
>
> B1=1
> B2=1
> B3=Blank
> B4=A
> B5=Blank
> B6=Blank
>
> =SUMPRODUCT((B1:B6<>"")/**COUNTIF(B1:B6,B1:B6&""))
>
> Firstly take (B1:B6<>"")
>
> This will check B2:B30 is blank or not, If it is blank will give FALSE, if
> not will give TRUE. So will get like this
>
> {TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}
>
> First 2 cells are not blank;3rd one is blank, 4th one is not blank;5th &
> 6th are blank....
>
> COUNTIF(B1:B6,B1:B6&"")
>
> This will count B1:B6 against the same range B1:B6. So will get the count
> of the occurances of each values in the range.
>
> If you are adding *&"" *this will add a non zero length text value to the
> every cells. So blank will become a non zero length text value. If you are
> not adding &"" this will give you a #DIVO/0! error. Because all the count of
> blank cells will count as 0
>
> Without &"", the array will be,
>
> [2;2;0;1;0;0}
>
> With &"", the array will be,
>
> {2;2;3;1;3;3}
>
> See, All blank cells is changed to 3, count of all the blank cells in the
> range.
>
> First 2 cells counts are 2 (First cell value & 2nd cell value should be
> same);3rd one is blank;4th ones count is 1;5th & 6th cells are blank
>
> So, the Arry in SUMP will become;
>
> SUMPRODUCT({TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}/{2;2;3;1;3;3})
>
> TRUE will converted to 1
> FALSE will to 0
>
> So, here;
>
> {1/2;1/2;0/3;1/1;0/3;0/3}
>
> Which is;
>
> {0.5;0.5;0;1;0;0}
>
> {0.5+0.5+0+1+0+0}
>
> =2
>
> See the below link more about SUMPRODUCT;
>
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> HTH
> Haseeb
>
>
>
>
> --
>
> ----------------------------------------------------------------------------------
> 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
