COUNTIF Function in Google Sheets

COUNTIF Function helps to perform conditional counts depending on a condition. It’s a bit more advanced from the regular COUNT function in this regard and is a part of a group of similar other functions like COUNTA, COUNTIFS etc

In this tutorial we will take you through the all the important aspects of COUNTIF Function.

Purpose of COUNTIF

As indicated before COUNTIF helps us to get conditional counts.

But what does the above mean? For example, say we have below range of numbers, in Google Sheet. And we want to find out number of people with a weight of over 70 kgs.

To achieve this, we can simply use the below COUNTIF Formula.

=COUNTIF(D3:D10,">70")

We have detailed out more examples in subsequent sections. However, before that lets firstly let’s understand the syntax and associated constraints for COUNTIF Function in the following section.

Syntax and Parameter Definition

= COUNTIF(range, criterion)
  1. range: It’s the group of cells(like A3:A7, B2:B10 etc) which needs to be counted, against a specific criterion.
  2. criterion: If the rule or condition(like “>10”, “=5”) which needs to be satisfied for count to get registered. You can include comparison operators like <, > , <=, >=, = , <>

Important Points to Note:

  1. Range can have number or string values. However, if Range is string then criterion must be too and vice-versa.
  2. Criterion must be enclosed in double quotes like “=5”, “>7” , “STAR”
  3. One can easily use wildcard characters like *, ~ etc while specifying the criterion for a text condition
  4. Both Range and Criterion can be specified with cell references like COUNTIF(B3:B7,C1)
  5. COUNTIF is case-insensitive while checking for text criterions
  6. Empty cells in the range are excluded from the count operation

Expected Output

COUNTIF returns the count of number of cells, within the specified range which fulfill the condition specified in Criterion.

COUNTIF Sample

The output can very well vary basis the criterion specified.

It’s important to highlight here that COUNTIF can only take one Criterion at a time. If you want the count basis multiple criterion then it’s use COUNTIFS which is a better alternative.

Hope, it was clear till here. As you can see COUNTIF is pretty easy to understand once you have understanding of the basics.

Examples

Let’s now cover some practical examples to grasp the usage of COUNTIF better. We’ll touch upon both type of cases where Criterion is number based vs text based.

Count number of times a value is <,>,=,<> in the Range

Although we have already seen comparison operators in action before, lets cover it again in a bit more detail.

We have tried to cover all the operator Criterions in the below example.

COUNTIF Examples
COUNTIF Examples

Counting Blank or non-Blank Cells in a Range

From all that we have learnt till now this should be an easy one.

To Count Blank Cells only deploy the following logic.

=COUNTIF(range,"")

To Count Non-Blank Cells only deploy the following logic.

=COUNTIF(range,"<>")
Counting Blank Cells in Google Sheets

Count number of times a word occurs in a range – Exact Match

Say we want to know how many time “TOM” occurs in the range B2:B10. This can be done using the simple formula below:

=COUNTIF(B3:B10, "TOM")
Text Match using Countif

Note: As evident COUNTIF doesn’t take into account upper/lower case while matching. Count is 3 including both “TOM” and “Tom”

Count Number of Times a word occurs – Partial Match

Wildcard Characters in Google Sheet can be easily used when there’s a need to create partial text match conditions.

Say we want to count all cells where with Names Starting with TOM. We’ll use the Wildcard Character * to achieve this objective.

=COUNTIF(B3:B10, "TOM*")

When we put a Criteria like “TOM*” , it will matching anything which starts with TOM.

Partial Match Using CountIF

Visual Demo of ROUNDUP Function

Before we end here is a sample visual demonstration of COUNTIF Function in action.

COUNTIF Function Demo

That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Liked the content?? Then share the word!!!

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply