Today I will be showing how you can use the **COUNTIFS** function to count the number of cells satisfying one or more criteria from any range of cells in Excel.

COUNTIFS Function of Excel (Quick View)

**Table of Contents**hide

**Download Practice Workbook**

**Excel COUNTIFS Function: Syntax and Argument**

**Summary**

- Counts the number of cells in one or more given arrays that maintain one or more specific criteria.
- Can be both Array and Non-Array Formulas.
- Available from Excel 2007.

**Syntax**

The Syntax of the **COUNTIFS** function is:

`=COUNTIFS(criteria_range1,criteria1,...)`

**Argument**

Argument |
Required or Optional |
Value |

criteria_range1 | Required | The first array. |

criteria1 | Required | Criteria applied to the first array |

criteria_range2 | Optional | The second array. |

criteria2 | Optional | Criteria applied to the second array. |

â€¦ | â€¦ | â€¦ |

â€¦ | â€¦ | â€¦ |

**Notes:**

- Only one criterion and one range of values where the criterion will be applied (
**criteria_range**)Â is compulsory. But you can use as many criteria and as many ranges as you wish. - The
**criteria**can be a single value or an array of values. If the**criteria**is an array, the formula will turn into an**Array Formula**. - The
**criteria**and the**criteria_range**must come in pairs. That means if you input**criteria_range 3**, you must input**criteria3**. - The lengths of all the
**criteria_ranges**must be equal. Otherwise, Excel will raise**#VALUE!**Error. - While counting, Excel will count only those values that satisfy all the
**criteria**.

**Return Value**

Returns the total number of values in the array that maintain all the given criteria.

**Special Notes**

- When the criterion denotes equal to some value or cell reference, just put the value or the cell reference in place of the criteria.

Like this:

`=COUNTIFS(C10:C14,C10:C14,80)`

OR

`=COUNTIFS(C10:C14,C10:C14,A2)`

- When the criterion denotes greater than or less than some value, enclose the criteria within an
**apostrophe (â€śâ€ť)**

Like this:

`=COUNTIFS(C10:C14,C510:C14,">80")`

- When the criterion denotes greater than or less than some cell reference, enclose only the greater than or the less than symbol within an
**apostrophe (â€śâ€ť)**and then join the cell reference by an**ampersand (&)**symbol.

Like this:

`=COUNTIFS(C10:C14,C10:C14,">"&A2)`

- You can have partial matches too within the
**AVERAGEIFS**function.

For matching any one character in a specific place, use **â€ś?â€ť.**

For example, **â€ś?endâ€ť**Â will match **â€śbendâ€ť**, **â€śsendâ€ť** but not **â€śspendâ€ť **or** â€śendâ€ť.**

And for matching any number of characters including zero, use **â€ś*â€ť**.

For example, **â€ś*endâ€ť** will match **â€śendâ€ť**, **â€śbendâ€ť**, **â€śsendâ€ť**, **â€śspendâ€ť** all.

So the **COUNTIFS** formula will look like:

`=COUNTIFS(C10:C14,C10:C14,"?end")`

OR

`=COUNTIFS(C10:C14,C10:C14,"*end")`

**Excel COUNTIFS Function: 4 Examples**

### 1. Counting Cells with Single Criteria

Look at the set of data below. We have the studentsâ€™ record of a school named Sunshine Kindergarten.

We have the studentsâ€™ names in column **B**, their marks in Physics and Chemistry in columns **C** and **D** respectively.

Letâ€™s try to count how many students got at least 80 marks in Physics.

The Formula will be:

`=COUNTIFS(C5:C20,">=80")`

See, there are a total of 6 students who got at least 80 in Physics.

Formula |
Output |
Explanation |

=COUNTIFS(C5:C20,â€ť>=80â€ł) | 6 | Counts how many cells in the range have a value greater than or equal to 80. |

**2. Counting Cells with Multiple Criteria**

Now letâ€™s try to count cells with multiple criteria.

Letâ€™s try to count how many students got at least 80 in both Physics and Chemistry.

The formula will be:

`=COUNTIFS(C5:C20,">=80",D5:D20,">=80")`

See, there are a total of 4 students who got at least 80 in both the subjects.

Formula |
Output |
Explanation |

=COUNTIFS(C5:C20,â€ť>=80â€ł,D5:D20,â€ť>=80â€ł) | 4 | Counts the total number of cells in the array C5 to C20 which have values greater than or equal to 80 and whose adjacent cells in the array D5 to D20 have also values greater than or equal to 80. |

**Similar Readings**

**How to Use LINEST Function in Excel (4 Suitable Examples)****Use CORREL Function in Excel (3 Examples and VBA)****How to Use MEDIAN Function in Excel (4 Suitable Examples)****Use LARGE Function in Excel (6 Easy Examples)****How to Use SMALL Function in Excel (4 Common Examples)**

**3. Counting Grades with COUNTIFS Function (COUNTIFS Array Formula)**

Now letâ€™s try a different approach.

Letâ€™s try to count the number of students with each grade in Physics.

For your convenience, I am reminding you of the criteria for each grade.

Criteria |
Grade |

>=80 and <100 | A+ |

>=70 and <80 | A |

>=60 and <70 | A- |

>=50 and <60 | B |

>=40 and <50 | C |

>=00 and <40 | F |

Before writing the main formula, see we have made this table in Excel too.

Now we select all the cells in the empty column, enter this **Array Formula** in the first cell and then press **Ctrl + Shift + Enter**.

`=COUNTIFS(C5:C20,G5:G10,C5:C20,H5:H10)`

See, we have got the number of students with each grade in Physics.

**Explanation of the Formula**

- If we break down the array formula
we will find six single formulas.`COUNTIFS(C5:C20,G5:G10,C5:C20,H5:H10)`

,

`COUNTIFS(C5:C20,G5,C5:C20,H5)`

`COUNTIFS(C5:C20,G6,C5:C20,H6)`

`COUNTIFS(C5:C20,G7,C5:C20,H7)`

`COUNTIFS(C5:C20,G8,C5:C20,H8)`

`COUNTIFS(C5:C20,G9,C5:C20,H9)`

`COUNTIFS(C5:C20,G10,C5:C20,H10)`

returns the total number of cells in the range`COUNTIFS(C5:C20,G5,C5:C20,H5)`

**C5**to**C20**that maintain the criteria**G5**and**H5**.- Same for the rest of the five formulas.

Formula |
Output |
Explanation |

COUNTIFS(C5:C20,G5,C5:C20,H5) | 6 | Returns the total number of cells in the range C5 to C20 that maintain the criteria G5 and H5. |

COUNTIFS(C5:C20,G6,C5:C20,H6) | 4 | Returns the total number of cells in the range C5 to C20 that maintain the criteria G6 and H6. |

COUNTIFS(C5:C20,G7,C5:C20,H7) | 1 | Returns the total number of cells in the range C5 to C20 that maintain the criteria G7 and H7. |

COUNTIFS(C5:C20,G8,C5:C20,H8) | 1 | Returns the total number of cells in the range C5 to C20 that maintain the criteria G8 and H8. |

COUNTIFS(C5:C20,G9,C5:C20,H9) |
1 |
Returns the total number of cells in the range C5 to C20 that maintain the criteria G9 and H9. |

COUNTIFS(C5:C20,G10,C5:C20,H10) |
3 |
Returns the total number of cells in the range C5 to C20 that maintain the criteria G10 and H10. |

**4. Counting Ranks with COUNTIFS Function (COUNTIFS Array Formula)**

The final task today.

Now we will try to find out the rank of each student according to their marks in a subject.

Letâ€™s try it with the marks in Chemistry.

Select a new column and enter this formula in the first cell of the column. Then press **Ctrl + Shift + Enter**.

`=COUNTIFS(D5:D20,">="&D5:D20)`

See, we have got the ranks of each student in Chemistry.

**Explanation of the Formula**

- If we break down the array formula
we will find 16 different formulas.`COUNTIFS(D5:D20,">="&D5:D20)`

,

`COUNTIFS(D5:D20,">="&D5)`

`COUNTIFS(D5:D20,">="&D6)`

`COUNTIFS(D5:D20,">="&D7)`

`â€¦`

`â€¦`

`COUNTIFS(D5:D20,">="&D20)`

counts how many values in the array`COUNTIFS(D5:D20,">="&D5)`

**D5**to**D20**have values greater than or equal to the value in**D5**. This is actually the rank of**D5**.- Same for the rest 15 formulas.

Formula |
Output |
Explanation |

COUNTIFS(D5:D20,â€ť>=â€ť&D5) | 10 | Counts how many values in the array D5 to D20 have values greater than or equal to the value in D5. This is the rank of cell D5. |

â€¦ | â€¦ | â€¦ |

â€¦ | â€¦ | â€¦ |

**Read More: The Different Ways of Counting in Excel**

**Common Errors with COUNTIFS Function**

Error |
When They Show |

#VALUE! | This shows when the lengths of all the arrays are not the same. |

**Conclusion**

Using these methods you can easily count the number of cells from an array maintaining any criteria in Excel. Do you have any questions? Feel free to ask us.