How To Count Rows Match Multiple Criteria In Excel

If you want to count the number of rows that match multiple criteria for multiple cells, you can use the excel function COUNTIFS to do it. This article will show you some examples of how to use the excel function COUNTIFS to count rows that match multiple criteria.

1. Excel Function COUNTIFS Syntax.

1. The function COUNTIFS has multiple parameter groups.
2. COUNTIFS(range1,criteria1,[range2],[criteria2],…)
3. Each parameter group contains 2 parameters that are the range parameter and the criteria parameter.
4. The first parameter group is required, and others are optional.
5. The parameter groups are AND relationships, which means only the rows that match all the conditions will be counted.
6. The function will return the counted rows number.

2. How To Count Rows Match Multiple Criteria In Excel.

1. Below is this article’s example cell data.
   

   	A	B	C	D	E	F
   1	How To Count Rows Match Multiple Criteria In Excel
   2	Student Name	Score	Sex		Statistics
   3	Jerry	100	Male		Score	>=80
   4	Kate	90	Female		Sex	male
   5	Tom	80	Male		Count	3
   6	Lily	60	Female
   7	john	70	Male
   8	Richard	80	Male
   9	Jackie	90	Female

2. Column A, B, C contains the student’s name, score and sex data.
3. We input the text >=80 in cell F3, and input the text male in cell F4.
4. And we can input the formula =COUNTIFS(B3:B9, F3, C3:C9, “=”&F4) in cell F5.
5. The above formula will be translated =COUNTIFS(B3:B9, “>=80”, C3:C9, “=male”).
6. The formula will only count the rows that score column’s value >= 80, and sec column value equals “male”.
7. When we press the enter key, it will display the number 3 in cell F5.
8. This is because there are 3 rows that contains text male in sex column and >=80 in score column.
9. If you want to count the number of rows that score column value >=80 and <=90, you can use the formula =COUNTIFS(B19:B25, “>=80”, B19:B25, “<=90”).