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.
- The function COUNTIFS has multiple parameter groups.
- COUNTIFS(range1,criteria1,[range2],[criteria2],ā¦)
- Each parameter group contains 2 parameters that are the range parameter and the criteria parameter.
- The first parameter group is required, and others are optional.
- The parameter groups are AND relationships, which means only the rows that match all the conditions will be counted.
- The function will return the counted rows number.
2. How To Count Rows Match Multiple Criteria In Excel.
- 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 - Column A, B, C contains the student’s name, score and sex data.
- We input the text >=80 in cell F3, and input the text male in cell F4.
- And we can input the formula =COUNTIFS(B3:B9, F3, C3:C9, “=”&F4)Ā in cell F5.
- The above formula will be translated =COUNTIFS(B3:B9, “>=80”, C3:C9, “=male”).
- The formula will only count the rows that score column’s value >= 80, and sec column value equals “male”.
- When we press the enter key, it will display the number 3 in cell F5.
- This is because there are 3 rows that contains text male in sex column and >=80 in score column.
- 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”).