# How to SUM weekly sheets onto a monthly summary

#### CaseyShort

##### New Member
I need to SUM weekly spreadsheets onto a monthly spreadsheet summary sheet according to personal name. Rather then clicking each individual cell to add I would like to know an easier way as the reference position can change on each weekly sheet and clicking pages of cells is very tedious. I have three columns of data beside a column of names on each seperate weekly sheet. What is the best way to add the data for each name onto a new monthly sheet? Please help.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi and welcome.

This will sum the name from cell A2 that matches in column A on three weekly sheets. It sums the value from column B where the name is matched in column A

Code:
``````=SUMIF(Week1!\$A:\$A, A2, Week1!B:B)
+SUMIF(Week2!\$A:\$A, A2, Week2!B:B)
+SUMIF(Week3!\$A:\$A, A2, Week3!B:B)``````

Is that what you want?

Hi - welcome to the board.

If possible, change the design of your workbook so that the "weeks' are all on one sheet, with and extra column in the data for tracking the week #, rather than a sheet each. Then you can straightforwardly use all the native functionality.

Hi,
Thankyou so much for your help, almost there. I changed your formula slightly to this

=SUMIF('[Week1]Aa WEEKLY SUMMARY'!A:A, A3, '[Week1]Aa WEEKLY SUMMARY'!B:B)
+SUMIF('[Week2]Aa WEEKLY SUMMARY'!A:A, A3, '[Week2]Aa WEEKLY SUMMARY'!B:B)
+SUMIF('[Week3]Aa WEEKLY SUMMARY'!A:A, A3, '[Week3]Aa WEEKLY SUMMARY'!B:B)+SUMIF('[Week4]Aa WEEKLY SUMMARY'!A:A, A3, '[Week4]Aa WEEKLY SUMMARY'!B:B)

(Aa Weekly Summary are our other mages for each month)

Its coming up with #VALUE! error, not sure what we need to change or what we may be doing wrong, do you think you might know??

The best way to make sure you have a correct cell reference in the formula is to click on that reference when making the formula.

Type this in a cell ...
=SUMIF(

With the cursor still in the formula bar, click on the Week1 sheet tab and click on its column A. Excel will fill in the entire reference for you in the formula. That way you can be sure there is no syntax error in the reference.

Type a comma after that reference and then click on the next reference you want in the formula until you completed everything in the entire formula.

Do you still get an error?

THANKYOU, lifesaver.

Replies
1
Views
69
Replies
2
Views
86
Replies
1
Views
111
Replies
3
Views
275
Replies
3
Views
154

1,203,646
Messages
6,056,531
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back