PDA

View Full Version : Solved: SUM a range based on user selection



phendrena
06-30-2012, 01:34 AM
Hello all!

I am having a blank brain moment.

I've not used excel properly for a few years now and if i don't do anything regularly then I forget it. So I am having a blank moment and would appreciate some pointers.

I am trying to sum a list of numbers.
There are 50 numbers in the list (lets say A1:A50)
Cell B1 is for the user to enter a number from 1-50
Cell C1 displays the result of the SUM

Here is the problem - I can't work out how SUM the range based on the users input in cell B1. One user might want to only SUM the first 17 numbers and another user the first 33 numbers.

So can anyone give me the simple solution that I am looking for.
Frankly I fell stupid for asking but my ageing brain forgets :(

Thanks,

Dave

xld
06-30-2012, 01:47 AM
Just get them to select the rows to sum and Excel shows the sum in the statusbar at the right.

phendrena
06-30-2012, 02:07 AM
Hi xld,

It's been a loooooooooooong time :)
Hope all is well :)

I've uploaded the spreadsheet for you to look at.
It's basically a helper sheet that I'm working on for quick calculations for a dodgy smartphone game that I play! The file itself will be used quite a bit on my smartphone using office mobile so it needs to 'work' based on my input rather than drag and sum etc

Thanks,

Dave

Cutter
07-01-2012, 04:57 PM
Try

=IF(W9="","",SUM(AD9:INDEX(HomelessGuy,W9)))

Aussiebear
07-02-2012, 02:35 AM
Whoa... I know its been a big day, but to run into all those colours.

BTW: There's a guy from the Tax dept.... needs an attitude adjustment, what's it going to take to do this?

phendrena
07-02-2012, 10:06 AM
Try

=IF(W9="","",SUM(AD9:INDEX(HomelessGuy,W9)))

Cheers! that works quite nicely.
Now, assuming I wanted to start the SUM further down the range how could I adapt that SUM?

Thanks,

Dave

phendrena
07-02-2012, 10:15 AM
Cheers! that works quite nicely.
Now, assuming I wanted to start the SUM further down the range how could I adapt that SUM?

Thanks,

Dave

I'm a fool!

=IF(W9="","",SUM(INDEX(HomelessGuy,AA1):INDEX(HomelessGuy,W9)))

Sorted :)