How to sum a range to a cell based on another cells value

[Dulock]

I have two worksheets, "values" and "keys"

each value has a corresponding key, if the value of the first key is 7, I need to sum keys b2:b8 (value + 1 because of header)

In other words, in case it wasn't clear already, is there a way I can reference (and increment) the value contained within "Values!B2" (Same sheet, I know I don't need to specify the sheet here, but writing it this way so all understand) in order to grab =sum(Keys!B2:B?) where ? = previously grabbed value + 1

Thank you kindly for any help!

[FDibbins]

the ? can be entered using the indirect() function. but perhaps you could also do the same thing using sumif() or sumifS()?

[Dulock]

Argh, I can't even use the reply feature properly tonight. It's no wonder Excel is beyond me...

Anyway, as I was saying before I deleted my reply...Thanks FDibbins, this certainly looks promising, and I can see how I can use this elsewhere, but I can't seem to make it work for me in this case...

Results:

=sum(keys!INDIRECT("B"&$B$24:"B"&$B$2))

gives me error: parse error (I never thought this was right, tried it last)

=sum(keys!B2:INDIRECT("B"&$B$2))

gives me error: unknown range name keys!B2

=sum(INDIRECT("keys!B"&$B$24:"keys!B"&$B$2))

gives me error: unknown sheet name <--also slightly ugly since I have to meaninglessly store a value of "2" somewhere in my sheet to reference...

---------- Post added at 11:41 PM ---------- Previous post was at 11:34 PM ----------

Argh, I was misreading something....and was concatenating entirely wrong.

=sum(INDIRECT("keys!B"&$B24&":keys!B"&$B2))

This works. I bet I can get the second one to work as well with this...

[CheshireCat]

Kinda ugly, but try this:

=SUM(INDIRECT(ADDRESS(2,2,1,1,"Keys")&":"&ADDRESS(B2,2,1,1)))

Replace any of the numerical values in the formula to control the range (e.g. B2 in second ADDRESS).

[Dulock]

*chuckles* okay, I admit, I don't understand how your thing works CheshireCat, but it does. I think I'm going to go to bed with a smile on my face now, and come back and study what in the world is going on in there after I've had some rest.

Thanks to both of you!

[Cutter]

Hello Dulock , and welcome to the forum.

Try this:

=SUM(Keys!B2:INDEX(Keys!B:B,Values!B2+1))

[Dulock]

=SUM(keys!B2:INDEX(keys!B:B,Values!B2+1))

Hi Cutter! Thanks for the help/suggestion, however (sadly) I get the same error with that one as I do with this one:

=sum(keys!B2:INDIRECT("B"&$B$2))

error: unknown range name keys!B2

It seems as if using a function call on the second half of the range confuses it. And I just cannot seem to figure out how to encapsulate it properly (single or double quotes, parentheses, etc...).

[Cutter]

Odd, what I gave you is copied straight from a test file and it's working for me.

Maybe because you have a range named the same as the sheet? Excel can be come confused at times.

Here's the test file: