LinkBack URL
About LinkBacksCan anybody explain me logic behind this formula
=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&""))
This formula will use to count the items (it will ignore the repeated items)
Please help me .
Can anybody explain me logic behind this formula
=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&""))
This formula will use to count the items (it will ignore the repeated items)
Please help me .
Hi Vishu,
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
will calculate the number of unique items.
How?
Say your range A1:A10 contains the values:
1,2,3,1,2,2,4,"","",""
The first part of the formula (A1:A10<>"") returns and array of TRUE or
FALSE depending on whether the cell contains an entry.
TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE
The second part COUNTIF(A1:A10,A1:A10&"") uses one of the more unusual ways
of using COUNTIF. Again it returns an array but this time each value in the
array represents a count of the numbers in the array using each value of the
array as a criteria.
2,3,1,2,3,3,1,3,3,3
That is, there are two values of 1, three of 2, one of 3 and three of blank.
The TRUE and FALSE array is divided by the count array:
0.5,0.33,1,0.5,0.33,0.33,1,0.33,0.33,0.33
The final three values are ignored (because of the FALSE) leaving
0.5,0.33,1,0.5,0.33,0.33,1
Add this array together and the result is 4.
sought from - bygsoftware.com
--
Regards,
vijay
"vishu" wrote:
> Can anybody explain me logic behind this formula
> =SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&""))
> This formula will use to count the items (it will ignore the repeated items)
> Please help me .
Hi Vijay,
Thanks for your help.
Can you please tell me if Cells A1:A10 has text charcters.(non numerics)
How this formula works
regards
vishu
"vijay" wrote:
> Hi Vishu,
>
> =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
> will calculate the number of unique items.
>
> How?
> Say your range A1:A10 contains the values:
> 1,2,3,1,2,2,4,"","",""
> The first part of the formula (A1:A10<>"") returns and array of TRUE or
> FALSE depending on whether the cell contains an entry.
> TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE
> The second part COUNTIF(A1:A10,A1:A10&"") uses one of the more unusual ways
> of using COUNTIF. Again it returns an array but this time each value in the
> array represents a count of the numbers in the array using each value of the
> array as a criteria.
> 2,3,1,2,3,3,1,3,3,3
> That is, there are two values of 1, three of 2, one of 3 and three of blank.
> The TRUE and FALSE array is divided by the count array:
> 0.5,0.33,1,0.5,0.33,0.33,1,0.33,0.33,0.33
> The final three values are ignored (because of the FALSE) leaving
> 0.5,0.33,1,0.5,0.33,0.33,1
> Add this array together and the result is 4.
>
> sought from - bygsoftware.com
> --
> Regards,
> vijay
>
>
> "vishu" wrote:
>
> > Can anybody explain me logic behind this formula
> > =SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&""))
> > This formula will use to count the items (it will ignore the repeated items)
> > Please help me .
Hi Vishu,
Text works the same as numbers. When you see the arrays it should be clearer. Follow these steps:
1. Enter the data suggested by Vijay into cells A1 to A10.
2. Enter the first array formula into an array between B1 and B10 by highlighting B1 to B10, type =A1:A10<>"" and whilst holding the Ctrl & Shift keys, press Enter. You'll notice the braces around the outside of the formula entered.
3. Enter the array formula =COUNTIF(A1:A10,A1:A10&"") into the cells C1 to C10 using the same method.
4. Enter the normal worksheet formula =B1/C1 into cell D1 and copy down to cells D2 to D10.
5. Enter a total in D11 to see the final result of the formula
6. You can then place other data (including repeated text) into cells A1 to A10 and observe the effect of the changes.
Last edited by John James; 03-28-2006 at 04:11 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks