INDEX and sum
 

I have a table of products in rows against years in columns with revenues in
the data array. I want to sum revenues for a 'x' year period starting at 'y'
year for product 'p' on a separate worksheet. So it's a 2D lookup.

Following useful tips from the community I was able to find the revenue for
'y' year for product 'p', using INDEX:
=INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0))
- where $C14 gives the product name and $Q$2 shows the year to look up.

I was even able to find the decrement figures 3 rows down using offset:
=OFFSET(INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0)),3,0)

but I couldn't make the summation to variable numbers of years across.

I had thought that I might be able to use the ADDRESS function and then sum
the range, but I couldn't find a way that would work.

Any suggestions ? I couldn't see this question asked previously ...

INDEX and sum
 

Hi,
If I didn't misunderstood you have in C14 the product name and in Q2 the
year and your information is in the sheet called New Products sales where in
cell A you have the product name, in cell B you have the year and you want to
sum column P

=sumproduct(--(C14=New Product Sales!$A$1:$A$1000),--($Q$2=New Product
Sales!$B$1:$B$1000),New Product Sales!$P$1:$P$1000)

change range to fit your needs

If this helps please click yes thanks

"doctorbarry1947" wrote:

I have a table of products in rows against years in columns with revenues in
the data array. I want to sum revenues for a 'x' year period starting at 'y'
year for product 'p' on a separate worksheet. So it's a 2D lookup.

Following useful tips from the community I was able to find the revenue for
'y' year for product 'p', using INDEX:
=INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0))
- where $C14 gives the product name and $Q$2 shows the year to look up.

I was even able to find the decrement figures 3 rows down using offset:
=OFFSET(INDEX('new product sales'!$A$1:$P$603,MATCH($C14,'new product
sales'!$A$1:$A$603,0),MATCH($Q$2,'new product sales'!$A$1:$P$1,0)),3,0)

but I couldn't make the summation to variable numbers of years across.

I had thought that I might be able to use the ADDRESS function and then sum
the range, but I couldn't find a way that would work.

Any suggestions ? I couldn't see this question asked previously ...