Microsoft Excel is a powerful data visualization and analysis program used all around the world. It uses spreadsheets to store, organize, or perform operations with formulas and functions.
Whenever you are dealing with huge data in Excel, and want to count rows based on given conditions, then we can do that with the help of the “COUNTIFS” function. The COUNTIFS function will get the count of total rows which comes under specified multiple criteria. This post will show you how to count rows that match two or more criteria in Excel. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
General Formula
Let’s see a simple formula to count the rows based on the given conditions on your Excel worksheet.
You can use the below-mentioned formula to count the rows that meet two or more conditions in Excel.
=COUNTIFS(range 1, criteria 1, range 2, criteria 2)
Syntax Explanations
In this section, we provide the explanation for the above-given formula, so that you can understand it more clearly.
- COUNTIFS – In Excel, this function will count the number of cells that match multiple criteria or conditions on the worksheet.
- Range – It represents the input values given in a worksheet.
- Criteria – This is the condition that helps to count the cells.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
How to Apply COUNTIFS Formula?
As we said earlier, the COUNTIFS function returns the total count of rows based on two or more criteria. For example, If you are running a bookshop and you need to keep track of the books that are sold on a daily basis. You need to filter the books which are sold to the maximum count, for that we suggest you use the Excel COUNTIFS formula.
In this situation, you can get the total count of rows that meets the given criteria. In the following image, we will show you the sample data of the bookshop.
Now, you need to enter the following formula in any cell where you want to get the output result.
=COUNTIFS(B3:B7,”Science”,C3:C7,”>50″)
After entering the formula, click on the Enter button to execute it and get the result. We hope that this scenario helps you understand the basic concept of the Excel COUNTIFS formula.
We hope that the above-given scenario makes you understand the working nature of the Excel COUNTIFS formula. If you still have some doubts and want to learn it with simple examples. Then jump to the below section where you can see alternate examples which will definitely help you.
Alternate Examples
You need to follow the below-given instructions to get the rows that match two or more criteria in Excel.
- Initially, you have to open your Excel workbook on your computer with the sample data or information.
- Let’s consider an example data for your reference. In the following image, you will see the list of fruits with their quantity.
- Once you have the data on the spreadsheet, you have to apply the given formula in the appropriate cell.
- We entered the formula in Cell E3 and hit the Enter button to get the result.
- Then, you have to apply the formulas to cells E4 and E5 to get the remaining results on the spreadsheet, as shown below.
- After completing the above steps, you can easily count the rows that match the given conditions in Excel.
Another Possible Way
You can find the total count of rows based on the given criteria without manually entering the formula in the Excel spreadsheet. Instead, you can also select it from the menu and complete the task quickly.
- For that, you need to open the Excel worksheet that has data. Here, we have listed some vegetables and their quantity. Let’s extract the result based on the given conditions.
- Go to the Formulas Tab, and click on the More Functions drop-down list under the Function Library section.
- From the drop-down menu, you have to select the Statistical option and then choose the COUNTIFS option from the sub-menu, as shown in the below image.
- It will display the Function Arguments dialogue, where you need to give the input range and criteria using the given browse button.
- Then, hit the OK button to get the result on the specific cell on your spreadsheet.
- At last, you will get the output count value in the selected cell, as shown in the below image.
This is another way to use the COUNTIFS formula in the Excel spreadsheet. Hope it is useful to you.
Bottom-Line
I hope that we made you learn the formula to count rows that match two or more criteria in Excel. We have provided a proper example for your quick understanding. So, apply the formula on your sheet and get the result based on your criteria. If you want to learn more Excel formulas, then visit our webpage Aawexcel.com.
To know more about this formula, we suggest you look for it on the Quora interaction webpage. On that page, you will get to know what others think about the Excel COUNTIFS formula. Here, we have provided the link to Quora’s official page: Excel COUNTIFS formula.
Video Tutorial
The following video will let you know how to apply the COUNTIFS formula in the worksheet in order to get the total count of rows based on given conditions.
Read Ahead:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Hi there, I’m Sridhar – an Excel enthusiast with over 10 years of experience working with software. I’m passionate about using Excel to solve complex problems and streamline business processes. Over the years, I have helped businesses of all sizes to improve their operations and save time and money.
Aside from working with Excel, I also enjoy writing and sharing my knowledge with others. You’ll often find me contributing to the AAW Excel blog, where I provide tips, tricks, and tutorials that are easy to understand for readers of all skill levels.
