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.

 

Los Angeles branch sales-costs in 2006

 

Q1

Q2

Q3

Q4

Total

sales

1500

2500

3500

4500

12000

costs

500

500

500

500

2100

 

 

New York branch sales-costs in 2006

 

Q1

Q2

Q3

Q4

Total

sales

1000

2000

3000

4000

10000

costs

500

500

500

500

2000

 

 

Result:

Los Angeles branch sales-costs in 2006

 

Q1

Q2

Q3

Q4

Total

sales

2500

4500

6500

8500

22000

costs

1000

1000

1000

1000

4100

 

 

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.

Los Angeles branch sales-costs in 2006

 

Price

Quantity

Total

Product 1

100.00

10

1000.00

Product 2

200.00

10

2000.00

Product 3

300.00

10

3000.00

 

New York branch sales-costs in 2006

 

Price

Quantity

Total

Product1

100.00

5

500.00

Product2

200.00

5

1000.00

Product3

300.00

5

1500.00

 

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:

Template sheet

 

Price

Quantity

Total

Product 1

100.00

(SUM)

1000.00

Product 2

200.00

(SUM)

2000.00

Product 3

300.00

(SUM)

3000.00

 

 

Follow Step1 ~Step6, the  result is:

Template sheet

 

Price

Quantity

Total

Product 1

100.00

15

1500.00

Product 2

200.00

15

3000.00

Product 3

300.00

15

4500.00