clunky formula i need help with

[Rkreutz]

I've got a formula that is supposed to look at 1 cell, then cross-ref that with another number and tally that with numbers under it. Like if my check cell is 10, i want to add up everything on my table 10 and under. Here's the formula as it stands...

=IF(Character!$C$3=4,Z87,IF(Character!$C$3=5,SUM(Z87:Z88),IF(Character!$C$3=6,SUM(Z87:Z89),IF(Character!$C$3=7,SUM(Z87:Z90),IF(Character!$C$3=8,SUM(Z87:Z91),IF(Character!$C$3=9,SUM(Z87:Z92),IF(Character!$C$3>=10,SUM(Z87:Z93),0)))))))

I would love a way to streamline this, as right now my formula is for numbers 4 to 10, but I could possibly go all the way to 20. You can see that when my target cell=4, I'm only adding 2 other cells, but at 10 I add 7. I can only nest 7 times, and I'm at my limit. Any help would be very appreciated!

Rudy

[DonkeyOte]

Perhaps

=IF(N(Character!$C$3)<4,0,SUM(Z$87:INDEX(Z$87:Z$93,Character!$C$3-3)))
Change references to suit (ie if you add more numbers change reference to Z93 accordingly)

You could also use OFFSET but it is Volatile, eg:

=IF(N(Character!$C$3)<4,0,SUM(OFFSET(Z$87,,,Character!$C$3-3,1)))

[Rkreutz]

I just input the first function and it works beautifully!

Thank you SO much!

Rudy