How to SUM weekly sheets onto a monthly summary

CaseyShort

New Member
Joined
Mar 14, 2011
Messages
3
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

CaseyShort

New Member
Joined
Mar 14, 2011
Messages
3
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??
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,956
Members
430,330
Latest member
drAli77

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top