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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

AlphaFrog

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

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top