Find the Summation Over a Distinct Category in an Excel Pivot Table

Pivot tables in Excel are an excellent tool for analyzing data. They help you to aggregate, summarize, finding insights and presenting a large amount of data in just a few clicks. It is very flexible and can be changed without much effort. You can create and modify pivot tables very quickly.

Pivot table helps to create a distinct list of items by summarizing the data. This helps a lot in reporting and analyzing data. You can quickly calculate and compare distinct sums in the pivot table which helps to find business insights. In this tutorial, you will see how pivot table helps create a distinct list of items and the use of pivot table for summation over distinct category.

Create a distinct list of items using Pivot Table

You can use the pivot table to create a distinct list of items. In the following example, you have the beverage sales data of eleven items for the 3rd quarter of the year.

There are 133 entries in this data each containing the Month, Items, Quantity and Sales for the 3rd quarter of the year. To create a distinct list based on quantity and sales for Items using pivot table you need to follow the next steps:

Click anywhere in the data.
Go to Insert > PivotTable.
Check if the range covers the data. Check New Worksheet. Click OK.
Look at the resulting pivot table worksheet, from the PivotTable Fields Menu on the right. You would need to set the fields into the appropriate labels.
Set Items to the Row Labels. Quantity and Sales to the Value Labels.
Right-click anywhere in the Sum of Sales column in the pivot table. Select Value Field Settings > Show Values As > Number Format > Accounting. Click OK twice.

This will create a pivot table containing quantity and sales for a distinct list of Items for our data set.

Using Pivot Table for summation of one column and maximum of another. In this section, you will use the pivot table to sum one column based on distinct values and find the maximum of another based on the same values.

You will use the SoftTech IT project management data set for this example. The data includes the spending record and estimation for different tasks.

There are 6 entries each having the fields Task, Project, Manager, Spent(1,000s) and Estimation(1,000s).

In order to calculate the sum of the amount spent based on Task and compare it with the Estimation for that Task, you need to perform the following steps:

Click anywhere in the data.
Go to Insert > PivotTable.
Check if the range covers the data. Check New Worksheet. Click OK.
View the resulting pivot table worksheet, from the PivotTable Fields Menu on the right. You would need to set the fields into the appropriate labels.
Set Project to Filter Labels, Estimation (1,000s) to Column Labels, Spent (1,000s) to Value Labels, Task, and Manager to Row Labels.
Go to Pivot Tables Tools > Design, click on Report Layout, and select “Show in Tabular Form”.
Click on Subtotals and select “Do Not Show Subtotals”.

This will show the sum of the amount spent based on Task and compare it with the Estimation for that task.

You can narrow down the results applying filters on any field as per your requirements. You can filter the data for Task, Manager, and Estimation by just clicking on relevant field filter and selecting the option.

To find out the sum of the amount spent by each Manager and compare it with Estimation, follow steps 1-4. In step 5, set Manager to Row Labels before Task and you will have the sum of the amount spent by each Manager compared with the Estimation for each task.

Pivot tables are a great way to summarize and aggregate data to model and present it. You can also sum up a column and compare it with the max of another for the same item. These functions make pivot tables the perfect go-to tool for data analysis.

There are many powerful features of Pivot Tables that could help you gain insights into your data. If you want to save hours of researching and frustration and get to the solution quickly, try our Excel Live Chat service! Our Excel experts are available 24/7 to answer any Excel question you have on the spot. The first question is free.