If pulling data from other documents is not enough and you'd rather do some math with all those records, Consolidate Sheets is the add-on to go with. It not only merges multiple Google sheets but also calculates numbers at the same time.

WHY CONSOLIDATE SHEETS

- Spares you monotonous copy-pasting and building Pivot tables or QUERY formulas.
- Consolidate data from any number of sheets, without opening each one. You just tick off the sheets you'd like to process, specify ranges if necessary, and Consolidate Sheets summarizes data from there.

4 merging options to CONSOLIDATE DATA OF DIFFERENT STRUCTURES:

  • consolidate by the header row
  • consolidate by first column labels
  • summarize data by both headers and column labels
  • consolidate by position

11 built-in consolidation functions for different summary reports:
SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS, STDEV, STDEVP, VAR, VARP.

As an extra, you will also choose where to place the consolidated data: 
  - new spreadsheet
  - new sheet
  - custom cells within the opened spreadsheet

and a way to get that summary:
  - as values
  - as a constantly updated formula

💾 SAVE FAVORITE SETTING
If you often summarize the same type of data, save your preferred settings as scenarios. Name each scenario, and with just one click, your data will be consolidated without repeating the setup process.

👥 SHARE SCENARIOS
Share your saved scenarios with teammates or between accounts by exporting and importing them. Whether you’re backing them up to your Drive or making them available to others, sharing ensures everyone can work with preconfigured setups right away.

30 DAYS OF FREE USE

Fully functional 30-day trial period. All payments are secure and include an unconditional 30-day money-back guarantee.

If you have any questions about the add-on, please post them here:
https://www.ablebits.com/docs/google-sheets-consolidate-data/ 
We'll be happy to assist!

Consolidate Sheets is a proud member of the Ablebits product family. We build add-ons that help you make the most of Google Workspace. Visit our official website to learn more:
https://www.ablebits.com/google-sheets-add-ons/index.php 
–––––––––––––––––––––––––––––––––––––
WHAT'S NEW
***** V1.4 (16 Dec 2024) *****
★ New features ★
- Scenarios. Save the options you use most often and give this set a name – this is your scenario. Once you run it, your data will be processed right away, and you won't need to go over the same steps again.
- Share saved scenarios. Export and import your saved scenarios to share with your teammates or just make their backups on your Drive or computer.

Improved:
- You can now explore more Ablebits add-ons from the corresponding Extensions menu.
- Minor UX and UI improvements.

Fixed:
- Couldn't process sheets that had a plus symbol (+) in their names.
- Couldn't process data that contained some non-printing characters.
- Minor UX fixes.

***** V1.3 (17 Apr 2023) *****
Improved:
- It's now possible to look for the files in the folder with Starred items of your Drive.
- The limit for total cells in a spreadsheet is expanded to 10 million.
- Minor UX and UI improvements.

Fixed:
- Treated the same headers written in different cases as distinct ones and calculated them separately.
- Сouldn't locate spreadsheets in Drive if your search name started with an apostrophe.
- Sometimes the formula didn't work if your data had a blank top-left cell.
- Range separators didn't change based on the spreadsheet locale.

***** V1.2 (17 Feb 2022) *****
Improved:
- Significant performance improvements in processing large amounts of data.
- Minor UX and UI improvements.

Fixed:
- The resulting formula didn't work if there were formula errors in your source sheets.
- Sometimes counted all cells in the resulting file incorrectly, hence, exceeded the limit and couldn't return the summary result.
- SUM and AVERAGE sometimes returned empty cells instead of '0'.
- In some cases, didn't select the range with your data by clicking the 'Auto select' button.
- The add-on couldn't paste data into the file without a set time zone.
- Errored if you selected empty sheets for consolidation.
- The link to contact our support didn't work.

***** V1.1 (5 May 2021) *****
★ New features ★
- Another way to consolidate data – with a formula! Have the result for SUM and COUNT returned as a formula to consolidate all existing and future records from the selected sheets. The formula is always connected to your original files: the resulting calculations will consider all changes and new values automatically. Make sure to visit the help page for more details.

Improved:
- Now it's possible to consolidate data from just one table.
- Automatically took the existing table as the custom location for the result.
- Minor UX and UI improvements.

Fixed:
- Didn't accept the custom place for the result if entered from the keyboard.
- Threw errors about incorrect ranges even if the sheet was not selected.
- Used to add only up to 10 files from Shared Drive.
- Allowed you to press the final 'Consolidate' button a few times making the tool throw errors.
- In some cases, counted the amounts of data incorrectly, hence, didn't notify about the large selection and extra time needed to process these cells.
- Errored after pressing 'Cancel' in the notification message about the large selection.
- Didn't work if you run the tool from a Chart sheet.
- 'Select range' didn't accept correct ranges after small ones.