• 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 to which of the 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:
  

☞ 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”.

If you don't have Kutools for Excel installed, click here to download for a 30-day free trial.

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 the formula:
  =COUNTIFS(C2:C8,">60",D2:D8,">80") OR =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 the formula:
  =COUNTIFS(B2:B8,"india",E2:E8,"a") OR =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 the formula:
  =COUNTIF(B2:B8,"india")+COUNTIF(B2:B8,"england") OR =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 the formula:
  =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.

---

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

Read More... Full Features 30-Day Free Trial... Purchase... 

---

Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Full Features 30-Day Free Trial... Purchase...