How to sum between two column ranges with a changing variable

[albardit18]

I am not sure how else to describe the formula I'm trying to create but here goes.

I have 2 products, product A and B. Each product has 4 stages along their development that increase their value by predetermined amounts (stage A-D). On a separate part of the worksheet, I would like to be able to sum the progress made on each product depending on a variable cell that the user selects.

In the example attached, the user would have selected Stage C from the dropdown and the formula I need to create would sum product 1 and 2 individually from Stage A to Stage C in cells H3 and H4 respectively.

The end result should be:
Product 1 = 254
Product 2 = 171

I am thinking a SUMPRODUCT formula of some sort would do the trick but my knowledge is rather limited.

Any ideas?

[AliGW]

Try this:

=SUM(OFFSET($B3,0,0,1,MATCH(H$2,$B$2:$E$2,0)))

Copy down.

[José Augusto]

Or you can use

Please Login or Register  to view this content.

and copy down

[mstwntd]

Hey,

Good question. This can be done many different ways. My solution is to "=SUM(B4:OFFSET(A4,0,MATCH($H$2,$B$2:$E$2),1,1))" in H3.

The match is a lookup on your defined value (how many columns from the beginning). The sum starts at b4 and wants to add the range identified by the offset. So... the offset says to start on A4, move 0 rows, move 3 columns to the right, and select 1 cell)

So add B4-(3 columns to the right).

The current assumption is that all products start at A and have the whole value of A. You can add more logic to formula to account for % completed in each section. So if only half done for C. It would add all of A and B and only 50% of C. I have included a section for this as well.

I hope this helps and solves your problem.

[albardit18]

Thank you very much! These work great. And learning how the OFFSET formula works will save me a ton of time in the future on similar scenarios.

[AliGW]

You’re welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.