COUNTIFS Function – Google Sheets

The COUNTIFS function is used in Google Sheets to count cells if they meet specific conditions. COUNTIFS checks cells for conditions and counts every cell that meets the criteria. Use the COUNTIF function if you have only one criterion.

This function must match all criteria. If you want to match with an OR condition, use this technique.

Get your copy of these examples in a live Google Sheet.

Purpose

This function returns the number of cells that meet specified criteria in a data set.

Syntax

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

  • criteria_range1 – The range to check for criterion1.
  • criterion1 – The condition to check for criteria_range1.
  • criteria_range2, criterion2... – [OPTIONAL] – Additional ranges and criteria to check.

Insert Math Symbols (Add-On)

Web banner showing math symbols from the Insert Special Characters Add-On
Insert Any Math Symbol with One Click

Similar Functions

COUNT – Count the numeric values in a data set

COUNTA – Count the non-blank values in a data set

COUNTIF – Count the cells that match a criterion

COUNTIFS – Count the cells that match multiple criteria

COUNTUNIQUE – Count the unique values

COUNTUNIQUEIFS – Count the unique values that meet multiple criteria

COUNTBLANK – Count the blank cells

Video Tutorial

YouTube player

Examples

Example 1 – Between Two Amounts

First, we will count all of the cells in A2:A5 that fall between 37 and 45. Counting numbers between two amounts is a relatively straightforward use of the function.

Counting values if they are between two numbers with COUNTIFS
Between 37 and 45

=COUNTIFS(A2:A5,">37",A2:A5,"<45")

The function counts two of the four numbers. Notice the quotes around ">37" and ">45". Even though these numbers aren’t text values, the COUNTIFS function requires quotes around the condition. Typically quotes are used in spreadsheet formulas when referring to text. Read more about this inconsistency here.

Example 2 – Between Two Dates

Next, let’s look at counting dates between two days.

Counting the number of dates between two days
Between February 17, 2022, and February 28, 2022

=COUNTIFS(A2:A5,">2/17/2022",A2:A5,"<2/28/2022")

Notice that we are placing the > and < symbols inside the quotes like ">2/17/2022". The function counts February 21 and February 24 which results in 2 days.

What if we had a mix of different data types?

Example 3 – Has a Word and Greater Than an Amount

Furthermore, COUNTIFS can use text values and numbers in the same function.

Matches a word and an amount
Contains the Word “Sunny” and is Greater Than or Equal to 75

=COUNTIFS(A2:A5,"Sunny",B2:B5,">=75")

Here we count all cells that include the string "Sunny" if the values in B2:B5 are also greater than or equal to 75. Next, we will take a look at boolean values.

Example 4 – Contains a Word and Has a Boxed Checked

COUNTIFS can also check boolean values against criteria.

Counting values if there is a certain word and the checkbox is checked
Has the word “Sunny” with any words before

=COUNTIFS(A2:A5,"Sunny",B2:B5,TRUE)

Boolean values are either TRUE or FALSE. The boolean values that COUNTIFS is checking can be directly typed into a cell, the result of a check box, or the output of a function. Since boolean values are not text, you do not surround them with quotes.

In the example above, we count the instance where the word is "Sunny", and the box is checked. Checked is the same as TRUE.

Example 5 – Doesn’t Contain Certain Letters and Is Less Than an Amount

Using COUNTIFS to count when it doesn't contain certain characters and is less than a number
Doesn’t Contain the Word “Sun” and is Greater Than 68

=COUNTIFS(A2:A6,"<>*sun*",B2:B6,">68")

Wildcards can represent different characters in Google Sheets. An asterisk (*) is any number of characters, a question mark (?) is any single character, and the tiled (~) escapes the asterisk or question mark, which tells Google Sheets to interpret it literally. In the formula above, we are looking for any string of characters that does not contain the letters sun in the Weather column and has a value greater than 68 in the Temp column.

Live COUNTIFS Examples in Sheets

Go to this spreadsheet for examples of the COUNTIFS functions shown above that you can study and use anywhere you would like.

Notes

  • Comparison operators such as >,<,= must be enclosed in quotes like “>45“.
  • All ranges in the function must be the same size.
  • Don’t enclose cell references in quotes.
    • If you are combining comparison operators and cell references, use an ampersand (&) to join them like A1&">45".
  • Text must be in quotation marks, such as “North”.
  • Text strings can contain wildcard characters ? and * . The * matches any characters and ? matches any one character. *apple would match green apple and red apple. Do? would match dog and dot.
  • There may be times when you want to count empty cells. You can do this by using quotes with nothing in between. Be sure not to use a space between the quotes, and be aware that invisible characters (AKA non-printing) are not blanks.

Related Posts

Count cells with dates between two days