Forum Discussion
Need 1 worksheet to add totals from multiple worksheets in the same file.
4 Replies
Try the following formula-
=SUM('*'!A1:A100)OK now that's COOL!!!! I had no idea you could use a wildcard in the sheet name. I created sheet names: "Sheet1", "Sheet2", "3", "Sheet4", "5", "Sheet6"
and then typed =SUM('Sheet*'!A1:B1) and on Sheet1 it was changed to: =SUM(Sheet2!A1:B1,Sheet4!A1:B1,Sheet6!A1:B1)
don't know how I will use this but very cool! Thx for sharing.
This question is very vague. The are many possible approaches that might be needed.
a) simply write formula like =Sheet1!A1 + Sheet2!B2 + ...
to make it easy you can simply type = then navigate to each corresponding cell you need and then type +
you could also use =SUM( Sheet1!A1, Sheet2!B2, ....) and similare to above you type =SUM( and navigate to each cell and then type a comma (,) and don't forget the ) at the end
b) if everysheet has the values in the exact same location then you can SUM over a 3d array:
=SUM( Sheet1:Sheet10!A1 )
this will sum the cells A1 on every sheet from sheet1 to sheet10
there are many other options and variations but those are the most common solutions
In addition to the suggestions by m_tarler​ , there's also the possibility that you could redesign and consolidate the multiple sheets--depending on the reason for their existence--into a single sheet.
It's not uncommon for people to design a workbook with a separate sheet for (say) bank statements for each month of the year, and then want a front sheet (or dashboard) to consolidate all income and expenses for the year.
It's actually a better use of Excel to have a single worksheet with all transactions for the year (or even multiple years) and use Excel's marvelous abilities to pull out, when needed, the transactions for a single month, or the transactions involving a single payee (your car payments), or the transactions for a category of expenses (utilities).
If something like that could make sense in your situation, why don't you share a bit more of what your workbook is about, why you have multiple sheets, etc.
