how can I extract the 149 to use in a seperate Sumproduct formula

How about if you extract that value to another cell and then reference that
other cell? It just adds more complexity to put it all into a single
formula.

Filtered range is A6:A20.

Array entered:

=INDEX(A6:A20,MATCH(1,(SUBTOTAL(3,OFFSET(A6:A20,RO W(A6:A20)-MIN(ROW(A6:A20)),0,1)))*(A6:A20<""),0))

Assume you enter that formula in A1. Then you can just refer to A1 in your
SUMPRODUCT formula:

=SUMPRODUCT(--(A7:A4620=A1),--(L7:L4620="Y"),K7:K4620)

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
In an autofilter -- If I filter on Column A (A5 is header)
and the first data row in Column A is cell A149, how can
I extract the 149 to use in a seperate Sumproduct formula

=SUMPRODUCT(--(A7:A4620=$A$149),--(L7:L4620="Y"),K7:K4620)

TIA,

Jim May