How to use the Google Sheets Count Function (The Easy Way!)
There are numerous reasons you may need to count values in a spreadsheet; inventory, payroll, accounting and more. As part of this, we might need to find the total instances or count within specific ranges. The Count function is our trusty function for the times that we need to do this in Google Sheets.
Google Sheets COUNT Function Syntax
The syntax for the COUNT Function is as follows:
=COUNT(value1, [value2, …])
=COUNT refers to the Google Sheet Function for counting cells in a range and
=Value refers to the cells or ranges for our function to count
This function will count every instance of a number. For example if we type =COUNT(1,1,1,1,3,2), this function will return the count of 6 because there are 6 numbers in total.
COUNT Syntax Uses
Within the parenthesis we can indicate several types of value formats:
Individual values separated by commas
Range of values
Several ranges of values separated by commas
Using The COUNT Function in Google Sheets (Examples)
A. Using COUNT function to Count Numbers in a Single Column
Imagine you have a long list of items, each with their own item code. It will be too time consuming to count these values manually. The COUNT function is the perfect solution for this.
1. Select an empty cell
Let’s use cell D2 for this example.
2. In the selected cell, type =COUNT(
Commonly Google Sheets will give a smart suggestion on which values to count. If this is what you wanted, press tab. If not, proceed with the instructions)
3. Select the range you want to count.
Do not forget the closing parenthesis ) after. You can also select the first cell in the range and press Ctrl+Shift+Down Arrow on windows or Command+Shift+Down Arrow on a Mac to identify the whole range.
TIP: You can count the full column A by using =COUNT(A:A) in this case since our headers are of non-numerical values they will not be counted. Remember COUNT only works with numerical values.
B. Using the COUNT Function to Count Numbers in Different Sections
Sometimes we will have messier datasets which can be harder to count. Our sample data set below has customer IDS in three different columns. A simpler way to count these will be by following the steps below:
1. Go to an empty cell
2. Input =COUNT(range1, range2, range3…)
Here we can use: =COUNT(A1:A22,D1:D22,G1:G15) or simply =COUNT(A:A,D:D,G:G)
3. Press Enter
This will now give us the total count of cells with numbers in multiple columns which is 59.
Other Variations of the Count Function
The COUNTA Function follows a similar format and function to COUNT. However, this formula will count all cells that are not empty; whether they contain numbers, text, symbols, and even errors. This is how to use the COUNTA Function in Google Sheets:
1. Select an empty cell
2. Use the following formula
=COUNTA(range) or =COUNTA(A1:I22)
3. Press Enter
Using the same dataset as before, we can see that the output of COUNTA is 173 compared to the output of COUNT which was 59.
COUNTBLANK is the opposite of COUNTA and will only count all cells with no value inside them. It follows the same form as the two previously discussed counting methods.
In this example we have some cells containing numbers, some cells containing letters and some cells left as blanks. Let us use the three counting functions we have so far and compare.
The three functions will bring out the following values
You can use these functions together or with other arithmetic functions to be able to get what you need.
COUNTUNIQUE will count only the number of unique values in a dataset. Therefore, for a number repeated in the same dataset, it will only be counted once. If we were to use this for the set (1,1,2,3,1,5,1,2) it will return a count of 4 while the COUNT function will return a count of 8.
COUNTIFS uses the syntax =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). This formula will only count cells in a determined range that meet certain criteria/criterion. You can use this to count very specific items.
Say you have a large set of numbers in a spreadsheet and want to count only the values greater than 400, this will be the formula to use.
Simply use the formula
And those are just some of the different ways you can count on Google Sheets. There are many more possibilities and several other combinations to which you can use these functions to create the optimal spreadsheet for your needs.