SUMPRODUCT Adaptation
 

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

SUMPRODUCT Adaptation
 

=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

SUMPRODUCT Adaptation
 

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

On Apr 1, 8:02 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
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.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200804/1

SUMPRODUCT Adaptation
 

Hi Bob,

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

Cheers,
Sam

Bob Phillips wrote:
=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)<""))

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

SUMPRODUCT Adaptation
 

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

vezerid wrote:
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

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