*hi
*
all of these are <goog_220687958>array formulas
The following formulas will return the number of distinct items in the range
B2:B11. .
The following formula is the longest but most flexible. It will properly
count a list that contains a mix of numbers, text strings, and blank cells.
=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""),
IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))
If your data does not have any blank entries, you can use the simpler
formula below.
=SUM(1/COUNTIF(B2:B11,B2:B11))
If your data does have embedded blank cells within the full range, you can
use the following array formula:
=SUM(1/IF(B2:B11="",1,(COUNTIF(B2:B11,B2:B11))))-COUNTBLANK(B2:$B11)
If your data has *only* numeric values or blank cells (no string text
entries), you can use the following formula:
=SUM(N(FREQUENCY(B2:B11,B2:B11)>0))
Have A Nice Time & Enjoy Life
Regards:
CMA Ankur Pandey
(Someone Different)
I'm not the best but i'm not like the rest~~
On Tue, Jul 5, 2011 at 10:37 AM, Dilip Pandey <dilipan...@gmail.com> wrote:
> 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
>
--
----------------------------------------------------------------------------------
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
