Categories: Ask a "how-to" question :

Need to sum cells from multiple spreadsheets into one total spreadsheet.

Showing 1-5 of 5 messages
Need to sum cells from multiple spreadsheets into one total spreadsheet. rbeitgal 3/13/09 3:09 PM
Can you please help me.
I have multiple spreadsheets with multiple worksheets. I need to add each cell in a worksheet in a specific row and column to a grand total spreadsheet with a matching worksheet.  I don't want to go into each cell and create an individual formula to add them up across worksheets.  That would take forever.  Is there a way to add cells from matching worksheets into one grand total.  Note: there might be words in some of the cells. ex. total
Thanks in advance for your assistance.
ex.
spreadsheet 1, worksheet 1, row 1, column a =12; row 2 column a = 5
                       ""       ""       row 1, column b =10; row 2 coumn b = 3
spreadsheet 2, worksheet 1, row 1, column a = 9; row 2 column a = 8
                       ""       ""       row 1, column b = 6; row 2 coumn b = 1
want to add spreadsheet 1 and 2 into spreadsheet 3
spreadsheet 3,  worksheet 1 row 1, column a =(12+9) =21; row 2 column a = (5+8)= 13
                       ""       ""       row 1, column b =(10+6) =16; row 2 coumn b = (3+1)=4
_________________________________________________________________
spreadsheet 1, worksheet 2, row 1, column a =11; row 2 column a = 4
                       ""       ""       row 1, column b =9; row 2 coumn b = 2
spreadsheet 2, worksheet 2, row 1, column a = 8; row 2 column a = 7
                       ""       ""       row 1, column b = 5; row 2 coumn b = 0
want to add spreadsheet 1 and 2 into spreadsheet 3
spreadsheet 3,  worksheet 2 row 1, column a =(11+8) =19; row 2 column a = (4+7)= 11
                       ""       ""       row 1, column b =(9+5) =14; row 2 coumn b = (2+0)=2
                                         
thanks

2009-03-16
I must be doing something wrong because excel keeps showing this as an invalid formula. 
"key_spreadsheet1" = assumed this the name of the first spreadsheet I want to grab the information from. ex. spreadsheet 1
"worksheet1"= assumed this is worksheet name in spreadsheet 1.  ex worksheet 1
Is the problem I have spaces in the name?
Is the problem that I used the double quotes and these are not suppose to be in the formula at all.
Are the spaces between the parenthesis important?
Are the quotes around "worksheet1!A1:b2" suppose to be around the range too?
 
thanks for your help
 
 

2009-03-17
Nope, I am using Excel. Is there anyway to do this in Excel.
Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. ahab 3/13/09 4:20 PM
Try:
In spreadsheet3 , sheet 'worksheet 1' cell A1:
=ARRAYFORMULA( N(ImportRange( "key_spreadsheet1" ; "worksheet1!A1:B2" )) + N(ImportRange( "key_spreadsheet2" ; "worksheet1!A1:B2" )) )

In spreadsheet3 , sheet 'worksheet 2' cell A1:
=ARRAYFORMULA( N(ImportRange( "key_spreadsheet1" ; "worksheet2!A1:B2" )) + N(ImportRange( "key_spreadsheet2" ; "worksheet2!A1:B2" )) )

Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. ahab 3/16/09 4:14 PM
I presume that by excel you mean Google Docs spreadsheet, as Microsoft Excel does not support the used functions.

"key_spreadsheet1" is the key value in the URL of the spreadsheet, not its name.
"worksheet1!A1:B2" is the sheet name and the range as you would have them in the spreadsheet, i.e. a sheet named worksheet1 and the range A1:B1.
Both parameters need to be inbetween double quotes.

E.g. to reference cell A1 in sheet 'Start' in  the spreadsheet "Copy of example get latest submit", with the URL https://spreadsheets.google.com/ccc?key=pPbcejygf5gbvQFKr3vtrQg&hl=en using an ImportRange function would be done like this:
=ImportRange( "pPbcejygf5gbvQFKr3vtrQg" ; "Start!A1" )

If the sheet name contains spaces these should also be used in the second parameter, however any single quotes should not be used. E.g. a sheet reference like 'Sheet number one'!A1 would be specified as "Sheet number one!A1"
 
Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. ahab 3/17/09 1:04 PM
This forum is for Google Docs questions. And I already stated the formulas used are not supported by Microsoft Excel. You better ask again in a forum with Excel specialists.
Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. K2HUNTERR 1/5/10 10:10 PM
I have a question about this. Say for example I have spreadsheets that are in sequence of dates i.e 1/2/10, 1/3/10, 1/4/10
and i want to tally cell A100 on sheet 1 of all three into one.  How do i go about doing this with out supplying the key each time a new spreadsheet is created i.e tommorrow and the next and so on... 
 
Kiel