How to SUM weekly sheets onto a monthly summary

[CaseyShort]

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.

 

[AlphaFrog]

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

=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]

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]

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]

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?

 

[CaseyShort]

THANKYOU, lifesaver.