subtotal on sumproduct

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sidata@hotmail.com - 31 Oct 2006 21:52 GMT

I have a Sumproduct formular that i got from off groups but need it to
work on filtered Data

=SUMPRODUCT((A3:A51<>"")/COUNTIF(A3:A51,A3:A51&""))

Have been trying to us SUBTOTAL(3,OFFSET(INDEX............
But am unable to to get it to work.

Could some one please show me what the formular should be.

Thanks

Reply to this Message

Don Guillett - 31 Oct 2006 21:59 GMT

have you tried SUBTOTAL? Look in the help index

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have a Sumproduct formular that i got from off groups but need it to
> work on filtered Data
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

Lori - 01 Nov 2006 16:28 GMT

Do you mean the number of unique values in a filtered list? The COUNTIF
approach will not work as it counts unfiltered data as well. If A is
the list (A3:A51 in your example) this formula should work (without
array entry):

=COUNT(1/FREQUENCY(MATCH(LOOKUP(ROW(A),ROW(A)/SUBTOTAL(3,OFFSET(A,ROW(A)-MIN(ROW(A)),0,1,1))/(A<>""),A),A,0),ROW(A)-MIN(ROW(A))+1))

The LOOKUP(.) part returns only the filtered items in the list

> I have a Sumproduct formular that i got from off groups but need it to
> work on filtered Data
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

Rate this thread: