support

Categorize and Summarize after Filtering

Given the below sales amounts of some liquors, make statistics on the two places of production from which the liquors have the highest sales amount among the two types of liquors with the top sales volume.
To make statistics according to the type of liquor, firstly, we need to sort:

Sort in Excel

Categorize and summarize by type to calculate the total sales amount of each type. The result is as shown below:

categorize and summarize

Then, sort the liquor types by the total sales amount. To do so, fold the level of type, and sort by the total sales volume descendingly. The result is as shown below:

Sort by total sales volume with Excel

Expand the result, and only copy the sales data of the two types of liquors with the top sales amount to the new worksheet for analysis in the next step. The result is as shown below:

Copy data to the new sheet in Excel

Since the statistics are to be made according to the place of production of liquor, sort the data ascendingly by the place of production:

business spreadsheet software-sort

Categorize and summarize by the place of production, and calculate the total sales amount of each place of production:

business spreadsheet software-categorize and summarize

Fold the level of place of production, and sort by the total sales amount descendingly. The result is as shown below:

Sort in Excel

Then, you can view the final result from the business spreadsheet software. If you need the details of the top 2 places of production from which the products have the highest sales amount, then you will have to sort it out manually.

By comparison, when using Traditional Business Spreadsheet Tool to handle the categorization and summarization after filtering, you can still complete the procedure easily despite the some complicated filtering and sorting operations. However, since you need to summarize and analyze the filtered data, you will have to copy the filtered data or remove the undesired data manually. If the data volume or the number of groups is relatively great, then the job would be hard, cumbersome, and error-prone.

import data to esCalc-the business spreadsheet software

Group by the type of liquor. Select to sort before grouping, and then calculate the total sales amount of each group in the grouping row:

group by type in esCalc

In esCalc, you can directly sort the groups. To do so, select D2 and sort descendingly:

Sort in esCalc

You can directly filter the groups:

Directly filter in esCalc

In esCalc, you can carry out the Regroup operation. To do so, select C4, perform the group option, and select the Sort before group and Regroup options. The settings on the Group panel is as shown below:

group setting in esCalc

As you can see from the above result, the grouping row and the bands in it will be regenerated after regrouping. And the expressions in the grouping rows will be recalculated.

Sort the grouping rows again:

sort group rows in esCalc

The desired result is the groups ranking the top 2. For details, you can filter the groups again.
In esCalc, the business spreadsheet software, because you can sort, filter, regroup, and perform other operations on the groups very conveniently, you needn’t to process data manually when handling the categorization and summarization after filtering. You can complete the data processing in steps. The operation in each step is purposeful and simple, and the result is clear.

numskull813
landamman407 animalism364 undetermindtion905 hellhag238 fetich178 viticulture137 fabulist203