SUMPRODUCT Adaptation

[Sam via OfficeKB.com]

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

 

[Bob Phillips]

=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)

 

[vezerid]

Maybe the construct:

OFFSET(Data,ROWS(Data)-70,COLUMN(A:A)-1,70,)

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

Does this work for you?
Kostis Vezerides

 

[Sam via OfficeKB.com]

Hi Bob,

Thank you ever so much for your time and assistance. Your formula is doing
the job Brilliantly!

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Kostis,

Thank you very much for reply and assistance. Unfortunately, your formula did
not provide the expected results. It gave the correct count in some places
but not all.

Bob's formula does provide the expected results.

Cheers,
Sam