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
