Formula for adding multiple sheets

m_tarler’s advice is excellent. If you have control over how your workbook is set up, combining all your transactions into one sheet (with an extra column for something like “Sheet origin” or “Month”) is absolutely the cleanest and most reliable approach. Then you can just use a PivotTable or SUMIFS to sum by name of place.

However, if you cannot change the multi-sheet structure (for example, if each sheet is generated separately and you must keep them apart), here’s an alternative step-by-step formula that builds on m_tarler’s VSTACK idea but is broken out for clarity:

=LET(

    AllData, VSTACK(Sheet1:Sheet5!A2:H1000),

    Names, CHOOSECOLS(AllData, 3),

    Withdrawals, CHOOSECOLS(AllData, 5),

    GROUPBY(Names, Withdrawals, SUM, 0, 0))

• Replace Sheet1:Sheet5 with your actual sheet names (e.g., Sheet1:Sheet10).
• Adjust A2:H1000 to cover all your data rows on each sheet.
• The GROUPBY function (available in Excel for Microsoft 365) will automatically list each unique name in column C and sum the withdrawal amounts from column E.

If you don’t have GROUPBY or VSTACK (older Excel versions), you can use a traditional approach:

1. Create a list of all unique names from all sheets using UNIQUE(VSTACK(...)).
2. Then use SUMIF across sheets, like:

=SUMIF(Sheet1!C:C, A2, Sheet1!E:E) + SUMIF(Sheet2!C:C, A2, Sheet2!E:E) + ...

(This becomes tedious with many sheets, so the VSTACK method is far better.)

Finally, when adding text in formulas, make sure names match exactly (e.g., “Joe Doe” vs “Joe Doe” with extra spaces). Using TRIM around names can help avoid mismatches.

My answers are voluntary and without guarantee!

Hope this will help you too.

So first off, the way your workbook is set up is the main issue.  So often people like to split entries across sheets based on months or years or even customers or employees but it is much better and more efficient to have all the data entries in 1 table (or sometimes linked tables like a table for employee info and another table for employee sales records where an employee or employee number in the sales records 'links' to a line in the employee info table).  That said there are some cases where you can't get around this workbook structure (e.g. it is exported from something else and you don't have control over that).  But if you can you should combine all the data entry tabs into 1 entry table and add a column for whatever variable you are using to define the tabs.  For example, let's say Sheets 1:5 are for Stores 1:5 then just create a single table and add a column named 'Store' and enter the corresponding identifier.  Then it is easy to filter that table for a specific Store or data entry like John Doe (or is it Joe Doe?).  You can even still have individual tabs for each store and just put a pivot table or use a formula like =FILTER( TableRange, CHOOSECOLS( TableRange, StoreColumn# ) = Store# )

you can also then again create a pivot table based on the person/place and it will put out the sum values.  Excel was designed to work this way and works well like that.

So that all said if you can't fix the workbook to make your life easier going forward, then you can use a trick like VSTACK to 'stack' the data from all the tabs and then apply the FILTER or either GROUPBY or PIVOTBY functions to get your result.  Here is a simple example:

=LET(data, VSTACK(Sheet1:Sheet3!A2:G100), GROUPBY(CHOOSECOLS(data,3),CHOOSECOLS(data,5),SUM))

and here is the output: