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

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

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)

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

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)

3. ## RE: Logic of this formula =SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1

Hi Vijay,
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)

4. 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1