How to Count Data Total Numbers in Groups by Excel?

In daily work, we often need to count the total number for something in different groups, for example count products sales in different seasons. When these products are saved in different groups, do we have a convenient way to do statistic? This article will show you a simple and intuitive way to count data in multiple groups by PivotTable in Excel.

First as we usually do, prepare a list or table for Product and Sales. See below:

Count Data Total Numbers 1

This is a quite simple table for statistic sales. We can see there are only three products. The sales are different on different dates. So how can we count the total sales for the two days? Let’s get started.

Count Data for Multiple Groups by A Pivot Table


Step 1: First, we need to insert a Pivot Table.

Click on Insert tab, PivotTable is displayed in the first place of Insert functions.

Count Data Total Numbers 2

Step 2: Click on PivotTable, now Create PivotTable is loaded.

Count Data Total Numbers 3

Step 3: In ‘Choose the data that you want to analyze’, check on ‘Select a table or range’, refer to the screenshot in step#1, we know the data range is from A1 to C7, so in ‘Table/Range’ field, we just select the range by mouse. After releasing the mouse, the area is automatically displayed in the textbox.

Count Data Total Numbers 4

Step 4: In ‘Choose where to place the PivotTable’, check on ‘New worksheet’ or ‘Existing worksheet’ depends on your requirement. In this case, for comparing with original table, we check on ‘Existing worksheet’ to make original table and PivotTable in the same worksheet. We insert PivotTable into cell A15 for example.

Count Data Total Numbers 5

Step 5: Click on OK. Now PivotTable Builder is loaded.

Count Data Total Numbers 6

Step 6: As we want to count the sales for products, so we drag Product to Rows field, Sales to Values field.

Count Data Total Numbers 7

Step  7: Then a new table is displayed. We can see the total sales for each product via the new table.

Count Data Total Numbers 8

Step  8: Obviously, we can edit PivotTable Builder and update table with more conditions. For example, we can drag Sales to Column field.

Count Data Total Numbers 9

Guess what table can we get? See below. We can get more details from below table.

Count Data Total Numbers 10

Above all, you can adjust PivotTable per your requirement, add or remove conditions to make the table meet your needs. In our life, we can use PivotTable to count data in different groups refer to above steps.

 

Related Posts

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Calculate Years Between Dates In Ms Excel

If you are an avid Ms Excel user, then you might have come across a task in which you needed to calculate the years between the dates; you might take it easy and do this task manually, which is also ...

Calculate Number of Hours between Two Times

Calculating the difference between two times might be a valuable statistic for subsequent computations or averages, whether you're producing a time sheet for staff or recording personal exercises. While Excel has a plethora of complex functions, including date and time ...

Calculate Loan Interest in Given Year

When you borrow money, you are supposed to repay it gradually. Lenders, on the other hand, want to be compensated for their services and the risk they incur by lending you money. That is, you will not just repay the ...

Calculate Interest Rate for Loan

The interest rate is the fee charged by a lender to a borrower and is expressed as a percentage of the principal—the lent amount. The interest rate on a loan is often expressed as an annual percentage rate, abbreviated as ...

Sidebar