Every Google Sheets user knows what the COUNT function is for. It returns the amount of numeric values in a data range. For counting not only numbers you can use COUNTA. And when your goal is to count data that match specific criteria, then you should go with either COUNTIF or COUNTIFS. Read on to learn how they differ and what you can do with both functions in real-life cases.

Do you prefer watching than reading? Then check out this video about COUNT, COUNTIF, COUNTIFS functions in Google Sheets by Railsware.

## COUNTIF function to count values by one criterion

The COUNTIF function is a hybrid of COUNT and IF functions. It allows you to perform data count based on a specific criterion.

### COUNTIF syntax

`=COUNTIF(data_range, "criterion")`
• `data_range` – Insert the range of cells to count.
• `criterion` – Insert the criterion (either number or string) to check the `data_range` against. Alternatively, you can reference a cell that contains a criterion (in this case, don’t use quotation marks).

## What you can count using COUNTIF

All the formula examples below are available in this spreadsheet.

### Count textual and numeric values by exact match

You can count the number of cells in the data range that contain a specific text or number.

#### COUNTIF formula example for textual values

`=COUNTIF('All orders'!F:F,"Olivia Jensen")`

Interpretation:

Count all the Olivia Jensen values (`criterion`) in the F column of the All orders sheet (`data_range`).

### Count textual values by partial match

You can count the number of cells in the data range that contain a specific part of the text. For this, you’ll need to use wildcard characters: * or ?.

#### COUNTIF formula example for partial textual match

`=COUNTIF('All orders'!E:E,"*burger")`

Interpretation:

Count all the values that contain the word “burger” (`criterion`) in the E column of the All orders sheet (`data_range`).

### Count numeric values by a logical expression criterion: greater, less or equal

You can count the number of cells in the data range that contain a numeric value that is less than, greater than or equal to a specified number. These logical criteria can be combined with each other.

#### COUNTIF formula example for numeric values

`=COUNTIF('All orders'!H:H,">5")`

Interpretation:

Count all the values greater than 5 (`criterion`) in the H column of the All orders sheet (`data_range`).

### Count blank and non-blank cells

You can count the number of blank or non-blank cells in the data range.

#### COUNTIF formula example to count blank cells

`=COUNTIF('All orders'!A:A,"")`

#### COUNTIF formula example to count non-blank cells with any value

`=COUNTIF('All orders'!A:A,"<>")`

The syntax us the same as for Excel COUNTIF not blank.

#### COUNTIF formula example to count non-blank cells with a textual value

`=COUNTIF('All orders'!A:A,"*")`

### Can I count values by multiple criteria with COUNTIF?

In Excel, COUNTIF supports multiple criteria. In Google Sheets, the COUNTIF function accepts only one data range and one criterion. Some suggest the following trick to count values across multiple criteria with:

=COUNTIF(data_range, “criterion#1”)+COUNTIF(data_range#2, “criterion#2”)+COUNTIF(data_range#3, “criterion#3”)…

However, this will only return the sum of separate counts based on separate criteria, which is unlikely to be what you need.

Let’s not reinvent the wheel, since there is an out-of-the-box solution: COUNTIFS.

## COUNTIFS function to count values by multiple criteria

The COUNTIFS function is a hybrid of COUNT and IFS functions. It allows you to check multiple ranges with multiple criteria. The formula returns the count based on the criteria met.

### COUNTIFS syntax

`=COUNTIFS(data_range#1, "criterion#1",data_range#2, "criterion#2",data_range#3, "criterion#3",...)`
• `data_range#1` – Insert the range of cells to count.
• `criterion#1` – Insert the criterion (either number or string) to check the `data_range#1` against. Alternatively, you can reference a cell that contains a criterion (in this case, don’t use quotation marks).
• `data_range#2, "criterion#2",...` – Additional ranges and criteria to check. The number of rows and columns in additional ranges must be equal to those in the `data_range#1`

### COUNTIFS formula example

`=COUNTIFS('All orders'!G:G,">=10",'All orders'!F:F,"Mark Oliver",'All orders'!I:I,"<50")`

Interpretation:

Count the values that meet all the following criteria:
1. Values in the G column of the All orders sheet (`data_range#1`) that are greater or equal to 10 (`criterion#1`).
2. Values in the F column of the All orders sheet (`data_range#2`) that contain “Mark Oliver” text exactly (`criterion#2`).
3. Values in the I column of the All orders sheet (`data_range#3`) that are less than 50 (`criterion#3`).

In this tab, you can check out the formula example and compare it to separate COUNTIF formulas based on the mentioned criteria.

How do you use COUNTIFS for the same range?
To check multiple criteria in the same column range, you need to use ARRAYFORMULA + SUM + COUNTIFS and place your criteria in the curly braces

Let’s say you need to check multiple criteria in the same data range. For example, you want to count the values in the F column of the All orders sheet (`data_range`) that contain exact “Mark Oliver” (`criterion#1`) and the values that contain “Olivia Jensen” (`criterion#2`). You might have thought about the following:

=COUNTIFS(‘All orders’!F:F,”Mark Oliver”,’All orders’!F:F,”Olivia Jensen”)

Unfortunately, this won’t do because it acts like the AND function in Google Sheets: it’ll look for a value that is “Mark Oliver” and “Olivia Jensen” at the same time.

To check multiple criteria in the same column range, you need to use ARRAYFORMULA + SUM + COUNTIFS and place your criteria in the curly braces as follows:

```=Arrayformula(
Sum(
COUNTIFS('All orders'!F:F,{"Mark Oliver","Olivia Jensen"})
)
)```

## Use QUERY as a COUNTIFS alternative

Earlier we blogged about the Google Sheets QUERY function, which allows you to grab the data based on criteria and perform various data manipulations. You can use QUERY as an alternative to COUNTIFS:

• Use the `SELECT` clause combined with the `COUNT()` function to make a data count.
• Use the `WHERE` clause combined with the `AND` logical function to set up multiple criteria.

Here is how the QUERY formula for counting values based on the multiple criteria will look:

Check out the formula example in this tab. You may also be interested in how to use a combination of QUERY + IMPORTRANGE in Google Sheets.

## How to count values by partial match check with COUNTIF & COUNTIFS wildcards

There are two wildcards you can use to check the partial match of the data range: `?` and `*`

### Question mark wildcard (?)

The question mark (`?`) allows you to disguise one character you want to ignore. One question mark = one character. For example, `"???ed"` means that you’re looking for the 5-letter words that end with “ed“.

#### COUNTIF formula example with ?

`=COUNTIF('All orders'!E:E,"??????sandwich")`

Interpretation:

Count the values in the E column of the All orders sheet (`data_range`) that contain the 14-letter wording that ends with “sandwich” (`criterion`).

### Asterisk wildcard (*)

The asterisk (`*`) allows you to disguise any number of characters you want to ignore. For example:

• `"*sandwich"` – means that you’re looking for wordings that end withsandwich“.
• `"sandwich*"` – means that you’re looking for wordings that start withsandwich“.
• `"*sandwich*"` – means that you’re looking for any wordings that containsandwich“.

#### COUNTIF formula example with *

`=COUNTIF('All orders'!E:E,"*cheese*")`

Interpretation:

Count the values in the E column of the All orders sheet (`data_range`) that contain cheese in the wording (`criterion`).

Both examples are available in this tab.

Note: Use tilde sign (`~`) before an asterisk (`*`) or a question mark (`?`) to treat them as simple signs. For example, `"~**"` means that you’re looking for the values that start with an asterisk “*“.

## COUNTIF or COUNTIFS real life use cases

The examples above will unlikely be useful in real life. However, both COUNTIF and COUNTIFS can do the job when you need to build complex dashboards. Check out the following use case that will unfold the practical value of these functions.

### Use case: A live sales dashboard integrated with Pipedrive

#### Idea:

Import the data from a sales management software (we picked Pipedrive as an example) and build a sales dashboard stuffed with actionable metrics and data visualizations. Moreover, the dashboard will be updated automatically every day!

#### Step 1: Pipedrive data import

First we need to import our deals from Pipedrive to Google Sheets and automate data updates in the future. Coupler.io can handle these tasks easily. It’s a Google Sheets add-on to import data from versatile apps and online data sources. You’ll need to do the following:

• Find Coupler.io on the Google Workspace Marketplace and install it
• Set up a necessary integration

#### Step 2: Building the sales dashboard

The sales dashboard template, which you can download and adjust for your project, displays multiple sales metrics. But in terms of the COUNTIF usage, we’ll need the following ones:

• Geo chart with a conversion rate
• Deals breakdown (won, open, and lost)
• Win rate
##### Geo chart with a conversion rate

Conversion rate shows the ratio of won deals to total deals. To count the total deals, we’ll need the COUNTA function, whereas COUNTIF will help us return the won deals. Apply the FILTER function to sort results by the country and here is the formula:

```=COUNTIF(Filter(Deals!AL2:AL,Deals!Z2:Z=A61),"won")/
COUNTA(Filter(Deals!AL2:AL,Deals!Z2:Z=A61))
```
• `Deals!AL2:AL` – the status column of the imported data
• `Deals!Z2:Z` – the org_id.address column of the imported data
• `A61` – a cell with the country name
##### Deals breakdown (won, open, and lost)

A similar formula will return the number of deals by status. Here is how it looks for the lost deals:

```=IF(
ISBLANK(A19),
COUNTIF(Deals!AL2:AL,"lost"),
COUNTIF(Filter(Deals!AL2:AL,Deals!Z2:Z=A19),"lost")
)```
• `Deals!AL2:AL` – the status column of the imported data
• `Deals!Z2:Z` – the org_id.address column of the imported data
• `A19` – a cell with the country name
##### Win rate

Win rate is the ratio of won deals to closed deals, which in turn equals the sum of lost deals and won deals. COUNTIF will help us handle this complex calculation:

```=IF(
ISBLANK(A19),
IFERROR(
COUNTIF(
Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"won")/
(COUNTIF(
Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"won")+
COUNTIF(
Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"lost")),
"No closed deals"),
IFERROR(
COUNTIF(
Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"won")/
(COUNTIF(
Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"won")+
COUNTIF(
Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"lost")),
"No closed deals")
)```
• `Deals!CN2:CN` – the Year column
• `Deals!AL2:AL` – the status column of the imported data
• `Deals!Z2:Z` – the org_id.address column of the imported data