Formula for adding multiple sheets

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: