SUM based on categories

 

Total up categorical data; Add up all data that have the same category. This operation can be implemented across workbooks.

 

Steps to go:

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: Specify ‘Key Column’. It will take this column as category and add up all rows based on the categories.

Step 4: Specify ‘The first row’. It does it works from this row, ignores previous rows.

Step 5: Specify ‘The last row. It ends its work at this row, ignores later rows. Note: It is counted backwards.

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

Step 7: Click ‘Start’ button.

 

Example:

Let’s say we have sales reports of Chicago branch, L.A. branch and N.Y. branch. Now we need a report of the whole company.

 

Chicago Branch

Product

Q1

Q2

Q3

Q4

DVD Player

10

10

11

20

VCD Player

9

8

7

10

TV set

11

12

13

16

 

Los Angeles branch

Product

Q1

Q2

Q3

Q4

VCD player

11

10

11

20

microwave oven

9

8

7

10

icebox(Type1)

15

11

14

20

 

New York branch

Product

Q1

Q2

Q3

Q4

icebox(Type1)

8

8

8

15

icebox(Type2)

7

7

18

9

DVD player

7

8

8

15

microwave oven

9

9

9

9

TV set

8

8

8

8

 

Follow step 1~step 7:

Step 1: ‘C:\Reports’.

Step 2: ‘*(All worksheets)’.

Step 3: Obviously, column ‘Product’ is category here, so Key column is 1.

Step 4: The first row should be 3 because we need to ignore the first row and the second row.

Step 5: The last row is 1 (Counted backwards).

Step 6: Select ‘No’.

Step 7: Click ‘Start’ button.

 

Here is the result. You can see, all numbers are added up based on product.

DVD Player

17

18

19

35

VCD Player

20

18

18

30

TV set

19

20

21

24

microwave oven

18

17

16

19

icebox(Type1)

23

19

22

35

icebox(Type2)

7

7

18

9