Skip to content

Formulas to Count Rows that Matches Two or More Criteria!

Listen to this article.

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:

Count rows based on conditionsPin
Count rows based on conditions

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.

Sample dataPin
Sample data

Now, you need to enter the following formula in any cell where you want to get the output result.


Type the formulaPin
Type the formula

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.
Sample dataPin
Sample data
  • 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.
Enter the formulaPin
Enter the formula
  • 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.
Input RangePin
Input Range
  • 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.
Formulas TabPin
Formulas Tab
  • 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.
Select the rangePin
Select the range
  • 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.


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

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:


Leave a Reply

Your email address will not be published. Required fields are marked *

Share to...