NorthStar Analytics
Pricing

HiDocs High-Quality Ghost Documentation Theme

How to Use COUNTIF Function in Google Sheets?

How to Use COUNTIF Function in Google Sheets?

When working with Google Sheets, we may sometimes need to search within the table. We may need repetitive data. For example, we may want to know how many of x items in the sales table were sold. Fortunately, it's pretty easy to do in Google Sheets. With COUNTIF, we can easily reach the number of repeaters. Let's look at the usage areas one by one with the help of examples.

Where is COUNTIF used?

We will explain the COUNTIF usage areas with examples and examine the syntax.

Using COUNTIF in the text tables

Our table consists of texts. Here we wonder how many tables there are. For this;

  1. Come to the cell where you want to print the result
  2. Type =COUNTIF(A:A,"Tables")

A:A → The range of cells I want to search. I can specify a specific range, as well as select the column. Just click on column A. Here is the result our table has only two tables.

Using COUNTIF in numeric tables

Our table consists of numerical values. This is a sales high school. We wonder how many products were sold  3 units. For this;

  1. Come to the cell where you want to print the result
  2. Write =COUNTIF(A:A,3)

A: A→Range of cells I want to search.

There are 6 products sold 3 times in our table.

What if I'm curious about the number of products sold more than 3?

  1. Come to the cell where you want to print the result
  2. Type =COUNTIF(A:A,”>3”)

Likewise, we can find sales of less than 3.

As we can see, we can very easily search within a certain range, conditionally. If you just want to highlight instead of counting conditionally, you can use conditional filtering for this. You can find detailed information about this in this article→  “How to highlight duplicates in google sheets?”

Using COUNTIF in date tables

We may want to find out how many entries were made in our sales table on a date “x”. For this, we will use the COUNTIF function to find the date.

  1. Come to the cell where you want to print the result

2. Write =COUNTIF(A:A,” 8.11.2017”)

As we can count a certain date, we can have it counted before and after that date.

For before a certain date:

  1. Come to the cell where you want to print the result
  2. Write =COUNTIF(A:A,” <8.11.2017”)

For after a certain date:

  1. Come to the cell where you want to print the result
  2. Write =COUNTIF(A:A,” >8.11.2017”)

So far, we've done the counting on one condition. So, can we search with more than one condition? Yeah. If you want to look at the products sold more than x units before a certain date with the COUNTIFS function. You can refer to this article→"How to use COUNTIFS function in Google Sheets?"

How do we count blank and non-blank (text) cells with COUNTIF?

It works the same as syntax. We use “  “  for blank cells and “*” to find cells containing text.

To count empty cells:

=COUNTIF(A:C," ")

I said A:C, it selected until the end of the column and counted 2925 empty cells.

To count cells containing text:
=COUNTIF(A:C,"*")

He found 51 cells with text in the range A: C. Normally there are 78 non-blank cells.

How can we sum the conditional counted on different pages?

While doing conditional counting, we can use the same table as well as different worksheets. For this, we need to add two functions.

=COUNTIF(B:B,">6")+COUNTIF(quantity!A:A,">=7")

In this way, we can learn how many sales were made on the 1st page, and how many sales were made on the quantity page, equal to or greater than 7.

Make your business data transparent!
  • Subscribe to a package

    Subscribe and explore the limits of your business with data or thinking to expand your agency value proposition with Google Data Studio dashboards?

  • Request unlimited dashboards

    You can request as many dashbaords you might have and as many revisions as you might need. We create a queue of your tasks, the first tasks that come in are the tasks that we’ll deliver first.

  • Your Data Studio Department

    Optimise your business with complete transparency through dashboards or provide value-added Google Data Studio service to your agency clients.

Make your business transparent

The fastest easy-to-use Google Data Studio dashboards without any recurring cost.