Current Events > Any mediocre excel users on here? Need some help with the sum function...

(+) Yeah! (+)

So I have a column full of numbers I need to sum up, say A1:A10 are the numbers I want to sum.

However cell B1 has a number like 3 in it. The sum will go in C1.

Now, the tricky part is I only want to Sum the first X numbers of the A1:A10 range, where X = B1.

So if B1 = 3, I only want to sum the first 3 numbers.

Is there an easy way to do this? Basically something like:

=sum(A1:A"=B1")

if that makes sense...
---

Really just any help with defining a Cell, I know there is a way.

Like if B1 = 3, I want cell C1 to equal A3. So I can do it multiple ways:

C1: =A3

or

C1: = A'=B1'

Although that isn't right I don't think...
---

Use this formula:

=SUM(A1:INDEX(A:A,B1+COLUMN(A1)-1))

Sums number of cells in column A according to what value is in B1.
---

VGfreak96 posted...

Use this formula:

=SUM(A1:INDEX(A:A,B1+COLUMN(A1)-1))

Sums number of cells in column A according to what value is in B1.

Amazing!

If you have some time can you give me a quick rundown of how the index function works there to get the other half of the range? If not no worries
---

This would also work as simply

=SUM(A1:INDEX(A:A,B1))

or as

=SUM(A1:INDEX(A:10,B1))

The index function is rather powerful and fairly simple to use. It takes an array (in this case, A:A) and uses cell B1 to know how far down to count.

Note that in your spreadsheet, lets say the cells array that you wanted to reference began at A3 and ended at A12, you would have to change the formula to:

SUM(A3:INDEX(A3:A12,B1))

And the reference number (cell B1) could not be a number higher than 10, because that's the most amount of numbers in that array that could potentially be summed (A3:A12 is 10 rows)

You should look up coupling INDEX and MATCH. It'll change your life.
---

Twin3Turbo posted...

This would also work as simply

=SUM(A1:INDEX(A:A,B1))

or as

=SUM(A1:INDEX(A:10,B1))

The index function is rather powerful and fairly simple to use. It takes an array (in this case, A:A) and uses cell B1 to know how far down to count.

Note that in your spreadsheet, lets say the cells array that you wanted to reference began at A3 and ended at A12, you would have to change the formula to:

SUM(A3:INDEX(A3:A12,B1))

And the reference number (cell B1) could not be a number higher than 10, because that's the most amount of numbers in that array that could potentially be summed (A3:A12 is 10 rows)

You should look up coupling INDEX and MATCH. It'll change your life.

Thanks for the info!

Yeah I used to use excel all the time a couple years ago and remember using index/match instead of vlookup... but I haven't done any excel since until this morning, trying to remember all this stuff
---

Also, if you decide to use his formula

=SUM(A1:INDEX(A:A,B1+COLUMN(A1)-1))

I think that you should change it to

=SUM(A1:INDEX(A:A,B1+ROW(A1)-1))

But if it were my spreadsheet I would just opt for what I gave you earlier

=SUM(A1:INDEX(A:10,B1))
---

Nice, just looked up this index match. I'm gonna have to keep this in mind in the future
---

emblem boy posted...

Nice, just looked up this index match. I'm gonna have to keep this in mind in the future

Yep, sometimes vlookup or hlookup is enough but index/match can be far more dynamic if need be
---