=SUMPRODUCT(
--(INDEX(OFFSET(Data,ROWS(Data)-$K$17-1,COLUMN(A:A)-1,$K$17),0,1)=K$15),
--(INDEX(OFFSET(Data,ROWS(Data)-$K$17,COLUMN(A:A)-1,$K$17),0,1)=K$16),
--(INDEX(OFFSET(Data,ROWS(Data)-$K$17,COLUMN(A:A)-1,$K$17),0,1)<""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:820946d18255e@uwe...
Hi All,

I have a dynamic named range "Data" that spans 8 columns and many rows.
"Data" holds numeric values. "Data" starts at row 18, column "K". The
oldest
data is at the top / start of the dynamic range and the most recent is at
the
bottom / end.

At the moment the SUMPRODUCT formula below looks at all the rows in my
range
"Data", can it be adapted using an input cell to look at only the LAST
(most
recent) "x" number of rows ( "x" will vary) within my dynamic range
"Data".
For the adaptation "x" will equal the LAST 70 rows.

=SUMPRODUCT(--(INDEX(OFFSET(Data,0,COLUMN(A:A)-1),0,1)=K$15),--(INDEX(OFFSET
(Data,1,COLUMN(A:A)-1),0,1)=K$16)*(INDEX(OFFSET(Data,1,COLUMN(A:A)-1),0,1)
<""))

The formula looks for 2 specific numeric values to be in the same column
but
appearing one after the other.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200804/1