Hello Excel Experts!
I have a workbook with 50+ worksheets (the actual number of worksheets varies).
Each day new worksheets with unique names are added to the workbook, and other worksheets are deleted.
All worksheets have the same format: column "A" are dates, and column "B" are values. The dates in each worksheet can be the same or different from dates in other worksheets.
I want to add a "Totals" worksheet using the same format that sums the value for each day across all of the worksheets in the workbook.
I currently use a long formula with repeated XLOOKUP() + XLOOKUP() + XLOOKUP()...for each date in each worksheet in the workbook.
Every day I need to modify this formula by adding an XLOOKUP for the newly added worksheets and deleting XLOOKUPs for the worksheets that were removed.
Eventually, I will make a mistake due to the length of the formula containing 50+ XLOOKUPS and the daily frequency of changes.
I want to use the shorter and simpler formula like "=Sum(Sheet1:Sheet50!B2)" which would sum the value in cell B2 across worksheets 1:50, and eliminate the problem of referencing each workbook - however, since the date ranges are in Column A of each worksheet it is necessary to add date criteria to this SUM formula. How do I do this?
I've created a simplified example here for downloading:
docs.google.com
You will need to download it as an xlsx, otherwise it will open as google sheets and the formulas will throw errors.
Hopefully, I made myself clear. If not, please let me know.
Thank you!
I have a workbook with 50+ worksheets (the actual number of worksheets varies).
Each day new worksheets with unique names are added to the workbook, and other worksheets are deleted.
All worksheets have the same format: column "A" are dates, and column "B" are values. The dates in each worksheet can be the same or different from dates in other worksheets.
I want to add a "Totals" worksheet using the same format that sums the value for each day across all of the worksheets in the workbook.
I currently use a long formula with repeated XLOOKUP() + XLOOKUP() + XLOOKUP()...for each date in each worksheet in the workbook.
Every day I need to modify this formula by adding an XLOOKUP for the newly added worksheets and deleting XLOOKUPs for the worksheets that were removed.
Eventually, I will make a mistake due to the length of the formula containing 50+ XLOOKUPS and the daily frequency of changes.
I want to use the shorter and simpler formula like "=Sum(Sheet1:Sheet50!B2)" which would sum the value in cell B2 across worksheets 1:50, and eliminate the problem of referencing each workbook - however, since the date ranges are in Column A of each worksheet it is necessary to add date criteria to this SUM formula. How do I do this?
I've created a simplified example here for downloading:
Excel Sum Across Sheets w Criteria Question.xlsx
Totals Date - OK,Sum Values by Date (method 1 - OK),Sum Values by Date (method 2 - NOT OK Needs Date Criteria HELP),Sum Across Worksheets Using Criteria Hello Excel Experts! This is a simplified example of a real-life problem I have in Excel. I have a workbook with 50+ worksheets (the actual n...
Hopefully, I made myself clear. If not, please let me know.
Thank you!
