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

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

One way

=SUMPRODUCT(--(A7:A4620=OFFSET($A$5,MATCH(1,SUBTOTAL(3,OFFSET($A $6,ROW($A$6:$A$4620)-MIN(ROW($A$6:$A$4620)),,)),0),)),--(L7:L4620="Y"),K7:K4620)

needs to be array entered so you can change it into a regular SUM array
formula, also if you are using 4600 rows something it might be slow

I originally posted it here

http://tinyurl.com/38o7s4

when a poster wanted to refer to the first cell in a filtered list

It worked using your requirements when I did a little test

To make the sumproduct more elegant I would probably use this formula

=OFFSET($A$5,MATCH(1,SUBTOTAL(3,OFFSET($A$6,ROW($A $6:$A$4620)-MIN(ROW($A$6:$A$4620)),,)),0),)

in another cell then refer to that cell


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




--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)




"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

Thanks guys, this one (formula) is a life saver !!
Jim

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A7:A4620=OFFSET($A$5,MATCH(1,SUBTOTAL(3,OFFSET($A $6,ROW($A$6:$A$4620)-MIN(ROW($A$6:$A$4620)),,)),0),)),--(L7:L4620="Y"),K7:K4620)

needs to be array entered so you can change it into a regular SUM array
formula, also if you are using 4600 rows something it might be slow

I originally posted it here

http://tinyurl.com/38o7s4

when a poster wanted to refer to the first cell in a filtered list

It worked using your requirements when I did a little test

To make the sumproduct more elegant I would probably use this formula

=OFFSET($A$5,MATCH(1,SUBTOTAL(3,OFFSET($A$6,ROW($A $6:$A$4620)-MIN(ROW($A$6:$A$4620)),,)),0),)

in another cell then refer to that cell


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




--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)




"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