---
title: "COUNTIFS function in Excel: syntax and examples | ExcelSavvy"
description: "How to use COUNTIFS in Excel and Google Sheets: count rows that match several conditions, or values between two numbers."
canonical_url: "https://excelsavvy.com/functions/countifs"
meta:
  "og:description": "How to use COUNTIFS in Excel and Google Sheets: count rows that match several conditions, or values between two numbers."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~Q09VTlRJRlMgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBDT1VOVElGUyBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogY291bnQgcm93cyB0aGF0IG1hdGNoIHNldmVyYWwgY29uZGl0aW9ucywgb3IgdmFsdWVzIGJldHdlZW4gdHdvIG51bWJlcnMu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~Q09VTlRJRlMoY3JpdGVyaWFfcmFuZ2UxLCBjcml0ZXJpYTEsIFtjcml0ZXJpYV9yYW5nZTIsIGNyaXRlcmlhMl0sIC4uLik%2Cp_Ii9mdW5jdGlvbnMvY291bnRpZnMi%2Cs_wSQ4KOyKRmKNCwMm.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "COUNTIFS function in Excel: syntax and examples | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~Q09VTlRJRlMgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBDT1VOVElGUyBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogY291bnQgcm93cyB0aGF0IG1hdGNoIHNldmVyYWwgY29uZGl0aW9ucywgb3IgdmFsdWVzIGJldHdlZW4gdHdvIG51bWJlcnMu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~Q09VTlRJRlMoY3JpdGVyaWFfcmFuZ2UxLCBjcml0ZXJpYTEsIFtjcml0ZXJpYV9yYW5nZTIsIGNyaXRlcmlhMl0sIC4uLik%2Cp_Ii9mdW5jdGlvbnMvY291bnRpZnMi%2Cs_wSQ4KOyKRmKNCwMm.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **The COUNTIFS function**

Counts the rows that meet every one of several conditions. Use the same range twice to count between two limits.

=

COUNTIFS(

## **How to use COUNTIFS**

COUNTIFS counts the rows where every condition is true. Think of a staff list: how many people are in Sales and started after January 1? Each condition checks one column, and a row is counted only when it passes all the checks.

Unlike SUMIFS there is no range to add, so the formula is just pairs of range and condition: `**=COUNTIFS(A2:A10,"North",B2:B10,"Shoes")**`. Each pair checks its own column, all ranges must have the same number of rows, and you can use up to 127 pairs.

To keep values between two limits, use the same range twice: `**=COUNTIFS(C2:C10,">=200",C2:C10,"<=400")**` counts values from 200 up to and including 400. The same trick works for a period of dates. With the limits in cells, write `**">="&F1**`.

With a single condition you can use the shorter COUNTIF. To add up amounts for the rows that match, use SUMIFS.

## **Syntax**

```
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
```

<dl><dt>**criteria_range1 **</dt>
<dd>The cells the first condition is checked against.</dd>
<dt>**criteria1 **</dt>
<dd>The first condition.</dd>
<dt>**criteria_range2, criteria2 **(optional)</dt>
<dd>More range and condition pairs, up to 127.</dd></dl>

## **Examples**

E1`=COUNTIFS(<sup>1</sup>A2:A6,<sup>2</sup>"North",<sup>3</sup>B2:B6,<sup>4</sup>"Shoes")`

|  | A | B | C | D | E |
| --- | --- | --- | --- | --- | --- |
| 1 | Region | Product | Sales |  | **2** |
| 2 | North | Shoes | 500 |  | |
| 3 | South | Shoes | 300 |  | |
| 4 | North | Bags | 250 |  | |
| 5 | North | Shoes | 420 |  | |
| 6 | South | Bags | 180 |  | |

**In plain words:** Count the rows where <sup>1</sup>the region in A2:A6 is <sup>2</sup>North and <sup>3</sup>the product in B2:B6 is <sup>4</sup>Shoes.

E1=COUNTIFS(C2:C6,">=200",C2:C6,"<=400")

|  | A | B | C | D | E |
| --- | --- | --- | --- | --- | --- |
| 1 | Region | Product | Sales |  | **2** |
| 2 | North | Shoes | 500 |  | |
| 3 | South | Shoes | 300 |  | |
| 4 | North | Bags | 250 |  | |
| 5 | North | Shoes | 420 |  | |
| 6 | South | Bags | 180 |  | |

## **Common COUNTIFS mistakes**

- COUNTIFS rejects my second condition

  Every condition needs its own range in front of it. When both conditions are on the same column, repeat the range: `**=COUNTIFS(C2:C6,">=200",C2:C6,"<=400")**`.
- COUNTIFS returns #VALUE!

  The ranges are not the same size, as in `**=COUNTIFS(A2:A6,"North",B2:B7,"Shoes")**`. Make every range cover the same rows.
- COUNTIFS returns 0 when I ask for two values in the same column

  The conditions are joined with AND, and no cell is both North and South, so `**=COUNTIFS(A2:A6,"North",A2:A6,"South")**` gives 0. Add two COUNTIF formulas instead: `**=COUNTIF(A2:A6,"North")+COUNTIF(A2:A6,"South")**`.
- COUNTIFS counts fewer rows than I expect

  One of the conditions is failing on a hidden difference, such as a space at the end of a name or numbers stored as text. Test each pair on its own with COUNTIF to find the one that drops rows.

## **Practice COUNTIFS**

- [**1****COUNTIFS with two conditions**→](https://excelsavvy.com/practice/countifs/countifs-two-conditions)
- [**2****Count between two numbers with COUNTIFS**→](https://excelsavvy.com/practice/countifs/countifs-between-two-numbers)
- [**3****Count the orders between two dates**→](https://excelsavvy.com/practice/countifs/countifs-between-two-dates)
- [**4****COUNTIFS with the conditions in cells**→](https://excelsavvy.com/practice/countifs/countifs-criteria-from-cells)
- [**5****Count a cross-table with COUNTIFS**→](https://excelsavvy.com/practice/summary-tables/cross-table-countifs)

## **Read the guide**

- [**Excel test for a job interview: what it looks like and how to practice**](https://excelsavvy.com/learn/excel-for-job-interviews)
- [**Pivot tables explained, and the formulas that do the same job**](https://excelsavvy.com/learn/pivot-tables)
- [**Excel practice data: free datasets with tasks and answers**](https://excelsavvy.com/learn/excel-practice-data)
- [**Pivot table practice: 15 exercises with answers on real sales data**](https://excelsavvy.com/learn/pivot-table-practice)
- [**Excel inventory data: free practice file with tasks and answers**](https://excelsavvy.com/learn/excel-inventory-data)
- [**Excel customer data: free practice file with tasks and answers**](https://excelsavvy.com/learn/excel-customer-data)

## **Questions**

<details>

<summary>

### What is the difference between COUNTIF and COUNTIFS?

</summary>

COUNTIF takes one range and one condition. COUNTIFS takes one or more pairs of range and condition and counts the rows that meet all of them.

</details>

<details>

<summary>

### How do I count between two dates?

</summary>

Use the date column twice, with the start date in F1 and the end date in F2: =COUNTIFS(A2:A100,"&gt;="&amp;F1,A2:A100,"&lt;="&amp;F2).

</details>

<details>

<summary>

### How do I count rows that match one value or another?

</summary>

COUNTIFS joins its conditions with AND, so add two formulas: =COUNTIF(A2:A6,"North")+COUNTIF(A2:A6,"South").

</details>

<details>

<summary>

### How do I count rows where one cell is empty?

</summary>

Use "" as the condition for the empty cell: =COUNTIFS(A2:A6,"North",B2:B6,"") counts the North rows where column B is empty. Use "&lt;&gt;" for cells that are not empty.

</details>

## **Related functions**

- [**COUNTIF**](https://excelsavvy.com/functions/countif)
- [**SUMIFS**](https://excelsavvy.com/functions/sumifs)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/functions/countifs#webpage","dateModified":"2026-11-16","description":"How to use COUNTIFS in Excel and Google Sheets: count rows that match several conditions, or values between two numbers.","name":"COUNTIFS function in Excel: syntax and examples","url":"https://excelsavvy.com/functions/countifs","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/functions/countifs#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/1"},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/2"},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/3"},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/4"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/functions/countifs"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/functions/countifs#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://excelsavvy.com/functions","position":2},{"@type":"ListItem","name":"COUNTIFS function in Excel: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/functions/countifs#article","dateModified":"2026-11-16","description":"How to use COUNTIFS in Excel and Google Sheets: count rows that match several conditions, or values between two numbers.","headline":"COUNTIFS function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~Q09VTlRJRlMgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBDT1VOVElGUyBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogY291bnQgcm93cyB0aGF0IG1hdGNoIHNldmVyYWwgY29uZGl0aW9ucywgb3IgdmFsdWVzIGJldHdlZW4gdHdvIG51bWJlcnMu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~Q09VTlRJRlMoY3JpdGVyaWFfcmFuZ2UxLCBjcml0ZXJpYTEsIFtjcml0ZXJpYV9yYW5nZTIsIGNyaXRlcmlhMl0sIC4uLik%2Cp_Ii9mdW5jdGlvbnMvY291bnRpZnMi%2Cs_wSQ4KOyKRmKNCwMm.png","@type":["Article","TechArticle"],"author":{"@id":"https://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/functions/countifs#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/functions/countifs#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"What is the difference between COUNTIF and COUNTIFS?","acceptedAnswer":{"@type":"Answer","text":"COUNTIF takes one range and one condition. COUNTIFS takes one or more pairs of range and condition and counts the rows that meet all of them."}},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I count between two dates?","acceptedAnswer":{"@type":"Answer","text":"Use the date column twice, with the start date in F1 and the end date in F2: =COUNTIFS(A2:A100,\">=\"&F1,A2:A100,\"\u003C=\"&F2)."}},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I count rows that match one value or another?","acceptedAnswer":{"@type":"Answer","text":"COUNTIFS joins its conditions with AND, so add two formulas: =COUNTIF(A2:A6,\"North\")+COUNTIF(A2:A6,\"South\")."}},{"@id":"https://excelsavvy.com/functions/countifs#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"How do I count rows where one cell is empty?","acceptedAnswer":{"@type":"Answer","text":"Use \"\" as the condition for the empty cell: =COUNTIFS(A2:A6,\"North\",B2:B6,\"\") counts the North rows where column B is empty. Use \"\u003C>\" for cells that are not empty."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~Q09VTlRJRlMgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBDT1VOVElGUyBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogY291bnQgcm93cyB0aGF0IG1hdGNoIHNldmVyYWwgY29uZGl0aW9ucywgb3IgdmFsdWVzIGJldHdlZW4gdHdvIG51bWJlcnMu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~Q09VTlRJRlMoY3JpdGVyaWFfcmFuZ2UxLCBjcml0ZXJpYTEsIFtjcml0ZXJpYV9yYW5nZTIsIGNyaXRlcmlhMl0sIC4uLik%2Cp_Ii9mdW5jdGlvbnMvY291bnRpZnMi%2Cs_wSQ4KOyKRmKNCwMm.png","inLanguage":"en-US","url":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~Q09VTlRJRlMgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBDT1VOVElGUyBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogY291bnQgcm93cyB0aGF0IG1hdGNoIHNldmVyYWwgY29uZGl0aW9ucywgb3IgdmFsdWVzIGJldHdlZW4gdHdvIG51bWJlcnMu%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~Q09VTlRJRlMoY3JpdGVyaWFfcmFuZ2UxLCBjcml0ZXJpYTEsIFtjcml0ZXJpYV9yYW5nZTIsIGNyaXRlcmlhMl0sIC4uLik%2Cp_Ii9mdW5jdGlvbnMvY291bnRpZnMi%2Cs_wSQ4KOyKRmKNCwMm.png"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
