Google Sheets count cells

There are some rules to follow depending upon data range contains number or text values while specifying the criterion in the COUNTIF function.

Table of Contents Show

How to enter the COUNTIF function in Google Sheets
Count of Sales Greater Than $400
Count of Sales Equal to
Count of Text value or string
Count of Text value using Wildcard
How do I count cells quickly in Google Sheets?
How do I count cells with text in Google?
Is there a count function in Google Sheets?
What is the formula to count cells with text Google Sheets?

If the data range contains numbers:

Comparison operators, such as > (greater than), >= (greater than equal to), < (less than), <> (not equal to) are used in expression to check the criterion with number. The expression must be enclosed in double quotation marks, such as “<=100”
Equal sign (=) is not used in an expression to check equal values, such as equal to 100
In expressions using comparison operators and cell references, the comparison operator is joined to the cell reference using the ampersand (&).
Comparison operators must be enclosed in double quotation marks, whereas cell references are not enclosed in double quotation marks, such as “>=”&A1

If the data range contains numbers:

Text string must be enclosed in double quotation marks, such as “Grapes”
Text strings can contain “?” and “*” wildcard characters. To match one character “?” wildcard is used, and to match multiple contiguous characters “*” wildcard is used, such as “P?T” or “*es”

How to enter the COUNTIF function in Google Sheets

When you enter any function in Google sheets, an auto-suggest box pops up to follow that function syntax instead of a dialog box in Excel. Like when we will enter COUNTIF function in Google Sheets auto-suggest box will popup; containing syntax, example, summary related to COUNTIF function and explanation of each part of function as shown below.

In this article, we will discuss the COUNTIF argument with some criterion for more clarification. Please follow these steps to enter COUNTIF function in Google Sheets.

Keep your cursor in a cell where you want to show your calculations, like cell E1
Enter “=” sign and type COUNTIF function in this active cell E1, an auto-suggested box pops up
Select the range of cells which you want to text again your criterion
Then enter comma “,”
Enter criterion expression to apply at selected range
Finally, press the ENTER key on the keyboard to enter the closing bracket.

Suppose we have sales dataset of some inventory items and we want to use COUNTIF function in Google Sheets to perform the following calculation on this data set.

Count of Sales Greater Than $400

Now we want to test the sales range of B2:B9 again this criterion expression of “>400”. As data range contains numeric values, so we will place the express in double quotation marks, as shown below

You can see the COUNTIF function has tested the selected sales data and counted the values against specified criterion where sales figures are greater than $400 and returned the result as 2

=COUNTIF(B2:B9,">400")

Now we will use comparison operator and cell reference in criterion expression to count sales that are greater than the value mentioned in cell reference, such as D2.

We will join comparison operator and cell reference by using an ampersand (&). Comparison operator will be placed in double quotation marks, whereas cell references will not be enclosed in double quotation marks, such as “>”&D2

=COUNTIF(B2:B9,">"&D2)

Count of Sales Equal to

IF we want to count the sales figure found in a data range that is exactly equal to a specified value, say $200, the COUNTIF function will be as follows;

=COUNTIF(B2:B9,200)

Here, we will not use the equal sign “=” as comparison operator in criterion expression and criterion numeric value will not be enclosed in double quotation marks.

Count of Text value or string

We can count the number of times a text value or text string appears in data range in Google Sheets using COUNTIF functions;

=COUNTIF(B2:B16,"Utensil")

Criterion text value will be enclosed in double quotation marks, and cell reference of that value will be used in the formula without double quotation marks, like;

=COUNTIF(B2:B16,D2)

Here you can see that COUNTIF has returned the output as number of times (5) a text value is found in data range to test for.

Count of Text value using Wildcard

You can count any instance of a text value in a data range using wildcards, like “*” and “?”. Wildcard search is not case sensitive and it will count any instance of text value, like

=COUNTIF(A2:A9,"*Bottled*")

As criterion text value can be found anywhere in a text string of selected range, then we will place “*” wildcard in start and end of this text value.

IF you have to count the occurrence of text value only at the end of a text string in the data range, then the formula would be;

=COUNTIF(A2:A9,"*Soda")

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

How do I count cells quickly in Google Sheets?

Highlight the cells. You'll see a small blue box in the lower right corner. Drag the blue box any number of cells down or across. If the cells form a series of dates or numbers, the series will continue across the selected cells.

How do I count cells with text in Google?

Using Google Sheets COUNTIF to count cells containing certain text. As discussed, you can use a COUNTIF formula to count the number of cells within a data range that contain a specific text or numeric value.

Is there a count function in Google Sheets?

Notes. Although COUNT is specified as taking a maximum of 30 arguments, Google Sheets supports an arbitrary number of arguments for this function. COUNT counts all numeric values in a dataset, including those which appear more than once. To count unique values, use COUNTUNIQUE .