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 row number.

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

  1. Below is this article’s example cell data.
    how-to-count-rows-match-multiple-criteria-in-excel
  2. Columns A, B, and C contain 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 the column’s value >= 80, and the Sex 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 contain the text male in the sex column and >=80 in the score column.
  9. If you want to count the number of rows that score column values >=80 and <=90, you can use the formula =COUNTIFS(B19:B25, “>=80”, B19:B25, “<=90”).

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.