How to do group by in Excel - COINTIF function example
The Microsoft Excel is one of the best tools to analyze data and I think every programmer should be familiar with this tool. There are a couple of genuine reasons for why I am asking you to learn and master Excel. Since most of us work in Windows and Excel is almost always available, knowing how you can use it for your data analysis and reporting can really make a difference. Managers, Directors, Clients, and People at a higher level just love reports and once your experience grows, your responsibility also grows and you need to create many reports or work on Excel sheet generated by others. It automatically becomes part of your life, especially if you are progressing on project management space.
To be honest with you guys, even though I have more than 12 years of experience and I first u used Excel more than 15 years ago, my Excel skills are still poor for my experience. Barring some essential concepts e.g. editing, filtering, sorting, removing duplicates, and some essential functions e.g. SUM, VLOOKUP etc, I don't know much.
One of the main reason of that is I never used Excel like many others use and never realize its importance. I was just happy that I know Excel but I hardly knew any advanced concepts, but things have changed in last year when I took a couple of courses to improve my Microsoft Skill e.g. Excel 2013 Fundamental Course from PluralSight.
I have been doing some data analysis work lately and I found Excel really useful for analyzing data. For example, suppose you got a list of books sold from your log file and now you want to know which book has sold how many times?
Earlier I used to store that data in a temp table in SQL Server then run the GROUP BY query, a lot of work for a simple thing.
But, as I said, things got improved in last one year, I come to know about many Excel functions, especially after joining anotherAdvanced Excel Formulas and Functions course at Udemy, which can do a lot for you and one of them is COUNTIF.
As the name suggests, this function is a combination of COUNT and IF, which means it can count based upon a given condition.
As per Microsoft Excel documentation, COUNTIF(range, criterion) counts the number of cells in a given range which matches the given condition. There is also a COUNTIFS() function which is similar to COUNTIF() but allows you to specify multiple conditions.
Microsoft Excel - COINTIF Function Example
In this article, I will share a tip which I have been using regularly. I have a list of values with me e.g. client name, books names, or item names and I need to find out the count of each item in the list. It's like GROUP BY in SQL and I'll show you how to do that in Excel using COUNTIF function. That's why I have also put the title, how to do a group by in Excel.Step 1: Copy List of values into Excel
Let's paste the list of books we found in a column in our Excel sheet e.g. copy them into the first column as shown below.List of books
Effective Java
Head First Java
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Head First Design Pattern
Head First Design Pattern
Effective Java
Head First Java
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Step 2: Remove Duplicates
In order to display the book and their count, we first need to remove duplicates. For that just copy the list of books into another column and click on remove duplicates as shown in the following screenshot.
This will remove all the duplicates on that column and you will have a list of unique values as shown below:
This is the easiest way to remove duplicate values from a list using Microsoft Excel but this functionality is only available from Microsoft Excel 2013. This is also one of my favorite Excel tip which I learned fromAdvanced Excel: Top Excel Tips and Formulas course from Udemy.
Step 3: Create Formula using COUNTIF function
Once you did that, just add a formula in the next column to show their count using COUNTIF function as shown below:=COUNTIF(A1:A35,D1)
This function tells Microsoft Excel that search in the range from A1 to A35 for the value present in D. I have given D1 because my unique values are in column D and they start from the first row because there is no header.
If your Excel sheet has a header then your formula should say D2 because that's your first value.
Btw, You don't need to type the range just type the function and then choose the starting cell and drag till the last cell in the column you have pasted the list of values.
For populating criterion, just select the value in the list without duplicate. Once it is done for one cell you can just drag it to apply the same formula for all the cells as shown below.
Btw, if you were to do this on another sheet then you need to include the sheet name as well in your formula.
If you look at it closely, this is what GROUP BY does in SQL. You give a list of values and then it divides them into the group and tells you the count of each value.
That's all about how to group a list of values in Excel and found their count. It's very useful tips while generating reports and I have found it using quite often. For example, you can use this trick to generate a sales report to find how many times each item is sold. You can then find the best seller or top 10 courses books etc. Btw, I still think, I don't know much of Excel but after realizing its importance in my career, I am also takingExcel fundamentals course to improve my Excel skills. you can join too.
Other Useful Tips and Resourcesfor Programmers
- Microsoft Excel - Advanced Excel Formulas and Functions (see)
- How to compare two lists of values in Excel? (tip)
- How to read/write Excel files in Java program? (see)
- How to enclose a list of values into single quotes in Excel? (tip)
- How to load data from a CSV file in Java? (program)
- Microsoft Excel 2016 Bible by John Walkenbach (book)
- 10 tips to become a better programmer (tips)
- Microsoft Excel - Excel from Beginner to Advanced (see)
Join the conversation