sum automatically between chosen worksheets

**jonathantan** · 09-08-2011, 10:50 PM

Can anyone help me how to sum same cell between chosen worksheets? let say i have 32 sheets in a workbook, named output, 1,2,3,4,5,6,7,8,......,31. On the "output" sheet, the result of the sum i want to have it on A2 cell. A1 and B1 are the cells where can i type which sheets to be calculated. let say if i enter 3 on A1 and 8 on B1, the result on cell A2 is the sum of a cell between sheet 3 to sheet 8. Thanx for the help.

**ron2k_1** · 09-08-2011, 11:42 PM

Welcome to the forum, this as an array may help. Note that it only works if the sheets are sorted in order (1,2,3,4... and not 1,3,2,4...). If they are not in order then you need to do a bit of more work.

=SUM(N(INDIRECT(ROW(INDIRECT(A1&":"&B1))&"!A1")))

Confirm with Ctrl + Shift + Enter

Note: INDIRECT does not seem to support 3D formulas, hence the above.

**alansidman** · 09-08-2011, 11:43 PM

If I understand what you are trying to do, sum all the A1 cells in 31 sheets.

Try: =SUM(Sheet1:Sheet31!A1)

**ron2k_1** · 09-08-2011, 11:48 PM

Originally Posted by alansidman

If I understand what you are trying to do, sum all the A1 cells in 31 sheets.

Try: =SUM(Sheet1:Sheet31!A1)

I guessing he wants a dynamic 3D formula, which forces you to use INDIRECT. But this formula will give you an #REF if you write it like this:

=SUM(INDIRECT("'"&A1&":"&B1&"'!A1"))

You will need an array similar to mines if you want to accomplish what he wants. Sum from the sheet number he inputs in A1 to the sheet number he enters in B2.

**jonathantan** · 09-09-2011, 12:03 AM

Originally Posted by ron2k_1

Welcome to the forum, this as an array may help. Note that it only works if the sheets are sorted in order (1,2,3,4... and not 1,3,2,4...). If they are not in order then you need to do a bit of more work.

=SUM(N(INDIRECT(ROW(INDIRECT(A1&":"&B1))&"!A1")))

Confirm with Ctrl + Shift + Enter

Note: INDIRECT does not seem to support 3D formulas, hence the above.

Thanx a lot Ron2k. what a fast reply. i have tried your formula n it seems, it doesn't calculated all. it only calculate the first sheet only. when i enter 1 on A1 and 2 on B1, the result is only the number on sheet "1" only. no matter what number i enter on B1, it shows the same result.

Please help me on this.

**ron2k_1** · 09-09-2011, 12:27 AM

It is an array formula which means that you just can't press Enter when entering the formula. Do this:

1. Copy the formula I gave you
2. Go to excel, Cell A2, and click on the formula bar, and paste there
3. Press and hold Ctrl and Shift, and while holding those press Enter.

**jonathantan** · 09-09-2011, 12:37 AM

Thankx ron2k. thanks a lot. you really make my life easier. it solved work well. really thank you to you.

**jonathantan** · 09-09-2011, 12:39 AM

how to mark this thread as solved? i dont know how. hahaha. really happy beacuse your help, ron2k.

**alansidman** · 09-09-2011, 06:47 AM

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save