Forum Discussion
Need 1 worksheet to add totals from multiple worksheets in the same file.
9 Replies
Create a summary sheet and use a 3D SUM formula to pull totals from the same cell across sheets (e.g., summing A1 from Sheet1 through Sheet5). It’s the simplest way to keep a running total.
Try the following formula-
=SUM('*'!A1:A100)I’ve seen proposals for using '*'!A1:A100 to sum across sheets—great creativity!
While wildcards are convenient here, they are risky. It is better, and above all safer, to use =SUM(Sheet1:Sheet3!A1) or explicit references to ensure reliability!
Excel deliberately restricts worksheet references in formulas to explicit names or contiguous ranges to ensure clarity, security, and performance. The wildcard character * is not supported, as it would lead to ambiguities and risks that contradict Excel's design principles. Microsoft doesn’t explicitly state, "We restrict wildcards in sheet references to avoid ambiguity," but their documentations implies it throughFor dynamic or non-contiguous sums, VBA or structured, defined names are, in my opinion, the most reliable solutions.
Wildcards (* and ?) in Excel are safe and powerful when used in specific functions designed for pattern matching.
Like Check if a cell contains a substring.
Example in SEARCH/FIND for partial text matches
=IF(ISNUMBER(SEARCH("*apple*", A2)), "Found", "Not Found") // Case-insensitive
=IF(ISNUMBER(FIND("*apple*", A2)), "Found", "Not Found") // Case-sensitive
Nevertheless, it's a nice approach, thank you for sharing🙂.
NikolinoDE although I don't disagree use of a wildcard for sheet reference seems risky, but to your point and as noted by myself, Lorenzo and Harun24HR, Excel REPLACES that wildcard with actual references so the final formula IS an explicit reference. So it is just a short cut to easily enter the formula.
That said, I agree that using any sheet range should be done with care. For example if you have Sheet1 through Sheet5 in order and you type: =SUM('Sheet1:Sheet5'!A1) then the following scenarios may occur:
- you move Sheet3 to be after Sheet5, then Sheet3 is no longer in the sum range.
- you move Sheet5 to immediately after Sheet1 then Shee2:Sheet4 are no longer in sum range
- you move Sheet5 before Sheet1 then the range changes to Sheet1:Sheet4
- and if you try to 'undo' the move you just did by moving it back where it was, it will not necessarily 'undo' but rather treat the move as a new move so scenarios 1&2 above are effectively 'undone' but scenario 3 is not.
So a must 'safer' reference formula would be to list every sheet out: =SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)
BUT.... even though sheet ranges have risks they also have advantages. So if you have a Tab for each employee and grouped by active and then inactive and SUM across all active employees, that make it very easy formula to write and see: =SUM('EmployeeA:EmplyeeZ'!A1)
And it is super easy to move an Employee from Active to Inactive by moving their tab out of the Active range to the Inactive range. BUT if you move your first or last active employee you will mess up that SUM. For example, if you move your last active employee to the end of the inactive list you just expanded the range to include all of those inactive employees. That is why when ever I have a workbook where I use this technique (note: I will usually 1st consider and/or recommend changing the whole workbook to have a master data input with emplyee as a column and then one or more reports/outputs that will show the information for an individual employee, but they won't or cannot do that ...) I will recommend or insist on adding 'helper' tabs like 'Start' and 'End' to define the Tab range to prevent or at least minimize the liklihood of moving tabs causing an issue.So I know I'm probably 'preaching to the choir' for the most part, but for the OP and others that may read this thread I thought it would be of value. And back to the interesting use of a wildcard in facilitating formula entry, I go back to my "I don't know how I would use this" statement as I am very careful how I would use any Sheet range and don't think this trick, although very cool, will have many, if any, practical use in how I do things.
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.
Similarly, created sheets 'Jan' & 'Feb' then on 'Sheet1' =SUM('*'!A1:A100) translated as =SUM(Jan:Feb!A1:A100)
don't know how I will use this but very cool! Thx for sharing
+1
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.
