Nashid
In this lesson, I’ll demonstrate these approaches to use Pivot Table to Count Rows in a Group in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Count Rows in Group Using Pivot Table in Excel with These 4 Steps
Here in this article, we will learn how to compare addresses in Excel using different approaches. To be exact, I’ve provided adequate instructions with 4 steps down below.
Step 1: Convert Datasets into Table
- Let’s define our dataset into a table before inserting Pivot Table. For doing that, we can use Ctrl+T. A small Box will appear to define the range of the table you want to create. For that, we have selected the whole table including the headers here. After that press OK.
Step 2: Insert Pivot Table
- After defining a table and selecting it, you’ll find a new Tab in n the ribbon named Table Design. From there select the Summarize with Pivot Table.
- Now a new window will appear, select the Table/Range then select the Existing Worksheet. After that, select the Location for pivot table then select OK we are selecting cell B18.
- As you can see in the picture below, we have created our Pivot Table.
Step 3: Open up Pivot Table Fields
- Click anywhere on the Pivot Table you’ll see options on your right popping up.
You’ll find this options on the Right Side of the window.
Step 4: Drag Fields to Appropriate Areas
- After that, drag the Group Column to the Rows section here we are dragging the National Team Column and the Values in the Value section, we are dragging the Player Name column here.
- After that, you’ll see results like the picture below.
How to Count Number of Instances in Excel
We will use UNIQUE and COUNTIF functions to count the number of instances for the same datasets here in this section. Follow the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, type in the following formula to know the distinct nationality names.
=UNIQUE(C6:C16)
- Press Enter to get the results and type in the following formula in the other column to get the count of players from that country.
=COUNTIF($C$6:$C$16,E6)
- After that press Enter and copy the formula down along the column to get all the answers.
📄 Important Notes
🖊️ Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.
🖊️ Use shortcuts to reduce time, use F4 for absolute value.
🖊️ While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
🖊️ Be careful while defining criteria to be accurate as the dataset.
📝 Takeaways from This Article
📌 Use of COUNTIF and UNIQUE functions.
📌 How to use Pivot Table to count rows in a group.
📌 How to count rows in groups with functions.
📌 Inserting Table using Ctrl+T.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this How to Pivot Table to Count Rows in a Group in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
