multiple worksheet summing help needed please

[debbyes]

I have searched the forum in hopes of finding an answer to my question. The answers I found need some embellishing please.

I have a workbook that I need to report on monthly as the data changes monthly. The basic "tool" doesn't change, but the monthly results do.

I need to be able to sum the values from different workbooks. For example, I want the sum of D13 from 10 different worksheets to appear on worksheet 12 in D13.

One answer said to start the function in the workbook needing the total, clicking from workbook to workbook selecting the cell needed and typing a comma before going to next workbook. What do I click after I select the last cell? How does it know I am done?

Another answer said to use Data>Consolodate. What do I select prior to Data? What do I select after Consolodate?

Another answer said to "add this for the workbook names you want"...'Drive\Directory\Folder\[workbookname.XLS]sheetname'!$n$43

I am completely lost.
Would someone please assist?

Thank you in advance!

[corrieann]

I need to be able to sum the values from different workbooks. For example, I want the sum of D13 from 10 different worksheets to appear on worksheet 12 in D13.

One answer said to start the function in the workbook needing the total, clicking from workbook to workbook selecting the cell needed and typing a comma before going to next workbook. What do I click after I select the last cell? How does it know I am done?

Starting in cell D13 on sheet 12, type "=" to begin your formula.
Select the sheet that has the first value you want to include and click on the cell.
Press "+"
Then select the next sheet and cell you would like to add.
Repeat this as many time as needed until you have pointed to each D13 cell on all your wooksheets.
When you are done, simply press enter.
Your formula would look something like:

=Sheet1!D13+Sheet2!D13+Sheet3!D13+Sheet4!D13+Sheet5!D13+Sheet6!D13

Of course yours will go all the way up to sheet 10.

Hope this helps!

[AlfD]

Hi!

I know how you feel about some of the "help".

Here is what my Help file tells me about 3-D formulae:

Refer to the same cell or range on multiple sheets
A reference that refers to the same cell or range on multiple sheets is called a 3-D reference.

Click the cell where you want to enter the function.
Type = (equal sign), enter the name of the function, and then type an opening parenthesis.
Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Complete the formula, and press ENTER.

This is a quick, shortcut way of doing it.
I'll try to translate:

1st line: Click on Sheet 12 cell D13
2nd line: Type =SUM(
3rd line: Click on the tab of Sheet 1
4th line: Hold down the shift key and click on the tab of Sheet 10
5th line: Click on D13
6th line: Type ) and press Enter

This you will surely admit is not difficult? But, as so often, it's the way they tell it...

Alf

[debbyes]

Thank you both, that was a great help!

My 2nd question.....

cell D15 on sheet 12 needs to be the total of the number of "y" in cell D15 on sheets 1-10, cell E15 on sheet 12 needs to be the total of the number of "n" in cell E15 on sheets 1-10.

I've tried countif, but have figured out that doesn't work with multiple worksheets. I've tried sum and sumproduct without luck.

Anyone help please??

Thank you!