subtotal on sumproduct

[Sidata]

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

 

[Don Guillett]

have you tried SUBTOTAL? Look in the help index

 

[Lori]

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