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.
The syntax for the COUNT Function is as follows:
=COUNT(value1, [value2, …])
Where
=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.
Within the parenthesis we can indicate several types of value formats:
Individual values separated by commas
=COUNT(1,4,3,7,2,12)
Range of values
=COUNT(A1:A123)
Several ranges of values separated by commas
=COUNT(B5:B22,C41:D65,A201:A245)
Full Column
=COUNT(B:B)
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.
Let’s use cell D2 for this example.
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)
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.
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:
Here we can use: =COUNT(A1:A22,D1:D22,G1:G15) or simply =COUNT(A:A,D:D,G:G)
This will now give us the total count of cells with numbers in multiple columns which is 59.
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
=COUNTIFS(A1:E20,">400")
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.
If you enjoyed this article, you might also like our article on how to use the Median IF on Google Sheets or our article on how to mail merge labels in Google Sheets.
Use our insurance renewal reminder software to easily set up custom reminders from your spreadsheet in just a few clicks.
