Add up the same cell across worksheets

 

Is there a way to add the same cell (ie.A1) from multiple worksheets without manually having to input sum (Sheet1:A1, Sheet2:A1, Sheet3:A1…)? Yes it is! With it you can merge multiple worksheets of the same format across multiple workbooks.

 

 

You can perform this function in two ways.

The first way: With template worksheet

Step 1: Enter ‘Folder where the Excel files locate’. The application will process all Excel files in this folder (Include subfolder).

Step 2: Enter ‘Worksheets to process’ to specify which worksheets should be process. You enter multiple worksheets name separated by ‘;’. You can enter wildcard here (‘*Sheet’ or ‘Sheet*’).

Step 3: Enter ‘Keep formulas in the result’. Normally you can select ‘No’.

Step 4: Click ‘Start’ button.

 

Example:

Here are sales-costs report of L.A. branch and N.Y. branch.

 

 

 

 

 

Result:

 

 

As you can see, all numbers are added up cell by cell.

(Note: You need to change the title to what you want manually.)

 

 

The second way: Without template worksheet

In the first way, it summarizes all number cells. But sometimes you may like to specify the cells to be summarized. At this time, you should do it in the second way.

 

            Step 1: Create a new worksheet, enter ‘(SUM)’ in the cells which you want to summarize. Save it.

Step 2: Enter ‘Folder where the Excel files locate’. The application will process all Excel files in this folder (Include subfolder).

Step 3: Enter ‘Worksheets to process’ to specify which worksheets should be process. You enter multiple worksheets name separated by ‘;’. You can enter wildcard here (‘*Sheet’ or ‘Sheet*’).

            Step 4: Specify the worksheet you create in step 1 as ‘Template worksheet’.

Step 5: Enter ‘Keep formulas in the result’. Normally you can select ‘No’.

Step 6: Click ‘Start’ button.

 

Example:

Here are sales report of L.A. branch and N.Y. branch.

 

 

There is a formula here: Total = Price * Quantity. You only need to summarize Quantity and keep other cells untouched. To do this, you can create a template sheet like below, enter (SUM) in all cells that you want to process:

 

 

Follow Step1 ~Step6, the  result is: