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.

 

 

 

 

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.