Sum across variable columns

[Nick Daniel]

Dear All,

I have a little problem I'm trying to overcome, and I'm trying not to use named ranges.

In the attached file, I have a simplified version of what I'm trying to do. I want to be able to input variable numbers of weeks into Cell B7 and have the Summed results appear against each Item below.

As an example, the result I am looking for if I entered '3' in B7 would be the sum of 3 weeks (columns B to D). If I entered '4' then 4 weeks (columns B to E).

Does anyone know of a formula I could use to achieve this?

Many Thanks,
Nick

[Nick Daniel]

I also forgot to mention that I will also be incorporating a VLOOKUP or MATCH function, as the Item Order in Cell A8 downwards will be different from the above Item order.

If anyone has any help for that it would be great, but the main issue is the sum across the range as I can hopefully go from there.

OFFSET possibly?

[DonkeyOte]

A few approaches... based entirely on your sample (ie unique items, results in same order etc...)

=SUM(B2:INDEX(B2:E2,$B$7))
copied down

or

=SUM(OFFSET(B2,,,1,$B$7))
copied down

(the latter is Volatile)

edit: given your edit - if the items are unique (appear once)

=SUM(INDEX($B$2:$B$4,MATCH($A8,$A$2:$A$4,0)):INDEX($B$2:$E$4,MATCH($A8,$A$2:$A$4,0),$B$7))

=SUM(OFFSET($B$2,MATCH($A8,$A$2:$A$4,0)-1,,1,$B$7))

[DonkeyOte]

On a final - just to cover all bases... if the items are not unique in the first table and thus you need to aggregate multiple rows simultaneously:

=SUMPRODUCT(($A$2:$A$4=$A8)*$B$2:INDEX($B$4:$E$4,$B$7))

[Nick Daniel]

Hi DonkeyOte,

That is absolutely brilliant and nails the problem spot on, thank you very much indeed!

Now I just need to look at it to work out how it does what it does for next time!

Kind Regards,
Nick