Excel COUNTIFS function - Count cells with multiple criteria - AND logic and OR logic

Syntax of COUNTIFS function in Excel

• Syntax of the COUNTIFS function
• Things to remember

Count cells that meet multiple specified criteria with COUNTIFS in AND logic

• COUNTIFS to count cells that meet multiple specified criteria in one range
• COUNTIFS to count cells that meet multiple specified criteria in different ranges

Count cells that meet multiple specified criteria in OR logic

• Add up COUNTIF or COUNTIFS results to count cells that meet any of the specified criteria
• COUNTIFS with an array constant to count cells that meet any of the specified criteria

Syntax of COUNTIFS function in Excel

As one of the statistical functions in Excel, COUNTIFS counts cells that meet single or multiple specified criteria across one or multiple ranges.

Syntax of the COUNTIFS function

The syntax of the COUNTIFS function contains following arguments:

• "criteria_range1" (required) refers to the first cell range where to apply the "criteria1".
• "criteria1" (required) refers to the condition to define which cells to be counted in "criteria_range1".
• "criteria_range2", "criteria2" (optional) refer to additional ranges and their associated criteria. Up to 127 pairs of range and criteria are allowed.

The COUNTIF function returns a numeric value – the number of cells that meet one or more specified criteria in associated ranges.

Things to remember

• The number of rows and columns of each additional range should equal those of "criteria_range1". The ranges can be the same, next to or not adjacent to each other.
•  
• In COUNTIFS formula in Excel, an Asterisk (*) can match any number of characters, while the Question mark (?)" matches any single character. If you need to count cells with the actual wildcard character - Asterisk (*) or Question mark (?), put a Tilde (~) before the wildcard character. For example, ~*, ~?.
• Quotes (“”) are required to enclose non-numeric criteria, while numeric criteria are ok without the quotes (“”). For example, 80, “>=80”, “a”, “india”.
• COUNTIFS function aren't case-sensitive. So, the string “India” or “INDIA” makes no difference to the result of the formula.
• To use a cell reference in COUNTIFS function with "comparison operators", you will have to put the operators in quotes (“”), and add an ampersand (&) in between. For example, “>”&B2.
• The order of comparison operators is important: = can only work by itself or after > or <.

Count cells that meet multiple specified criteria with COUNTIFS in AND logic

By default, the COUNTIFS function evaluates multiple criteria with "AND" "logic". The result of the COUNTIFS formula shows the number of cells that meet all the specified criteria. Here are some examples using COUNTIFS function to count cells in AND logic.

COUNTIFS to count cells that meet multiple specified criteria in one range

√ Note: Blank cells mean that the scores somehow haven’t been recorded properly. The score of 0 means that the student didn’t take the test.

To count the number of students whose English score is between 60 and 90 (including 60 and 90), here we have listed two ways.

• You can use a COUNTIFS formula:

  =COUNTIFS(C2:C8,">=60",C2:C8,"<=90")

  The result is "5"
•  
• Or, if you have Kutools for Excel installed, you can go to "Kutools" tab, find "Select", and click "Select Specific Cells" on the drop-down list. Set it as shown in the screenshot below:
  

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

☞ More approaches to accomplish the task: How To Count Number Of Cells Between Two Values Or Dates In Excel?

To count the number of valid scores (the cells are not 0 or blank), here we also have listed two ways.

• You can use the formula below:

  =COUNTIFS(C2:D8,"<>0",C2:D8,"<>")

  The result is "12"
  √ Note: "<>" in Excel means not equal to. ""<>0"" means not equal to 0; And ""<>"" means not equal to blank, or we can say, are not blank.
•  
• With "Kutools for Excel" installed, you can also go to "Kutools" tab, find "Select", and click "Select Specific Cells" on the drop-down list. Set it as shown in the screenshot below:
  
  √ Note: Kutools does not count blank cells by default, so under "Specific Type", you can just set the criteria as “"Does not equal to 0"”.

  Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

COUNTIFS to count cells that meet multiple specified criteria in different ranges

• To count the number of students who are "from the countries ending with the letter “a”" and "their names are started with "J"", use the formula:

  =COUNTIFS(A2:A8,"j*", B2:B8,"*a")

  The result is "2"
  √ Note: The wildcard character "asterisk (*)" matches any number of characters.
•  
• To count the number of students whose" English score and Math score are both over (not equal to) the scores of Eddie (values in C4 and D4)", use one of the following formulas:

  =COUNTIFS(C2:C8,">60",D2:D8,">80")

  =COUNTIFS(C2:C8,">"&C4,D2:D8,">"&D4

  The result is "3"
•  
• To count the number of students who are "from India (the value in B2)", and "with a grade of A (the value in E2)", use one of the following formulas:

  =COUNTIFS(B2:B8,"india",E2:E8,"a")

  =COUNTIFS(B2:B8,B2,E2:E8,E2)

  The result is "2"

Count cells that meet multiple specified criteria in OR logic

To count cells that meet multiple specified criteria in "OR logic" means to count the total number of the cells that meet criteria1, the cells that meet criteria2, and the cells that meet…, in another word, to count the cells that meet any of the (at least one) criteria. To do so, we have listed 2 ways below:

Add up COUNTIF or COUNTIFS results to count cells that meet any of the specified criteria

By adding up the numbers of cells that meet at least one criteria you specified would easily generate the total number of the cells. So, what do you need to do is to write several COUNTIF(S) formulas as you need, and then perform arithmetic operations.

• To count the number of students who are "from India (value in B2)" or "England (value in B3)", use one of the following formulas:

  =COUNTIF(B2:B8,"india")+COUNTIF(B2:B8,"england")

  =COUNTIF(B2:B8,B2)+COUNTIF(B2:B8,B3)

  The result is "4"
  √ Note: You can use "Kutools for Excel" if you have installed the professional add-in: "Kutools" > "Select" > "Select Specific Cells", select "OR" under the "Specific type".
•  
• To count the number of students with "English score or Math score over 80 (including 80)", use one of the following formulas:

  =COUNTIF(C2:C8,">=80")+COUNTIF(D2:D8,">=80")-COUNTIFS(C2:C8,">=80", D2:D8,">=80")

  The result is "5"
  √ Note: To minus COUNTIFS(C2:C8,">=80", D2:D8,">=80") here is to remove duplicates, which is the students with English and Math score both over 80.

COUNTIFS with an array constant to count cells that meet any of the specified criteria

While adding up COUNTIF or COUNTIFS results can be too big in size in formatting, we can combine the use of SUM and COUNTIFS functions with an array constant to count the number of different cells meeting associated criteria. Check the examples below:

• To count the number of students who are "from India or England", use the formula (one range):

  =SUM(COUNTIF(B2:B8,{"india","england"}))

  The result is "4"
•  
• To count the number of students who are "from India or America with the grade A, B or C", use the formula:

  =SUM(COUNTIFS(B2:B8,{"india","america"},E2:E8,{"a";"b";"c"}))

  The result is "4"
  √ Note: You need to use "semicolons (;)" in the second array constant. For the cases with more criteria, consider to use a "SUMPRODUCT" formula.