Logic of this formula =SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&

[Guest]

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 .

 

[Guest]

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

 

[Guest]

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

 

[John James]

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
& Alt 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.