Back to Blog

When to Use COUNTIF or COUNTIFS in Google Sheets: Real-Life Formula Examples

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. 

What is COUNTIF?

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.

Exact match for textual and numeric values

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). 

Partial match for textual values

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). 

Logical expression for numeric values

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). 

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,"<>")

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

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

Can I use COUNTIF for multiple criteria?

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.

What is COUNTIFS? 

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"})
  )
)

QUERY instead of COUNTIFS

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:

COUNTIFS formulaQUERY formula
=COUNTIFS('All orders'!G:G,">=10",'All orders'!F:F,"Mark Oliver",'All orders'!I:I,"<50")=QUERY('All orders'!F:I,"select count(F) where F='Mark Oliver' and G>=10 and I<50",0)

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.

COUNTIF & COUNTIFS wildcards for partial match check

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

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). 

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 “*“.

Where in real life you may need COUNTIF or COUNTIFS

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 Pipedrive deals into 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:

You can find all the steps described in the blog post “Export Data From Pipedrive To Google Sheets“.

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

For more about this interactive dashboard, read our blog post “How to Build Sales Tracker with Google Sheets“.

To wrap up

Now you know how to count with COUNTIF/COUNTIFS. Will this knowledge be helpful? We hope so and encourage you to check other Google Sheets functions we’ve described in the Coupler.io blog. The more you know, the better your reporting and dashboards will work. Good luck with your data!

Back to Blog

Access your data
in a simple format for free!

Start Free