To count if not blank in Google Sheets is useful if you want to count the cells that just have data in it (not blank).
Table of Contents
- How To Count If Not Blank in Google Sheets: 3 Ways
- 1. Using COUNTA Function.
- 2. Using COUNTIF Function.
- 3. Using SUMPRODUCT Function.
- How to Count If Not Blank in Google Sheets Using COUNTIF
The technique is commonly used when you have to count a specific cell range, especially if you have a large number of cells and you want an accurate count of the cells which are not blank.
For instance, you are running a small supermarket with 200 different items. You listed all the names of the items in Google Sheets. Using the techniques shared below, you can precisely obtain the right amount of stock ignoring cells that are blank and useless.
Ready to get started? Let’s dive right in! 😊
How To Count If Not Blank in Google Sheets: 3 Ways
1. Using COUNTA Function.
For the first approach when counting if not blank cells, we will be using the
COUNTA (Count All) Function.
The syntax or in other words the way we write the function is as follows:
=COUNTA(value1, [value2, ...])
Let’s break this COUNTA function down:
- = denotes the start of every formula written in Google Sheets.
COUNTA()is our function of which we need to provide its corresponding
value1we have to select the cell range that we want to count. This value is required to be given and the function will not work without it.
value2, and subsequent attributes ‘
...‘ are additionalcells to be included in the count. The maximum number of entries allowed is 30.
It’s a good idea to have some understanding of how to use the COUNTA function in Google Sheets before delving into using
COUNTA to count if not blank cells.
Now the big question in your mind at this point is, “what’s the use of learning this function?”
Well, when you are really sure that all your cells are blank, you can use the
COUNTA function to count the cells which are not blank.
Let’s take an example.
In the image below, cells A4:A6, A9, A11:A12, and A16 are empty. Using our finished
COUNTA function which is
=COUNTA(A2:A15)it will result in an answer of 8 which is correct as there are only eight cells which are not blankas shown below:
When you’re unsure of cells being blank, such as if you may have mistakenly entered an apostrophe or space in any of the cells, then
COUNTAwill count that too.
This is where
COUNTA is not ideal and the other solutions shared in this guide help circumvent.
In such a case, it’s best we rely on the COUNTIF function in Google Sheets which we will show you how below. 🙂
2. Using COUNTIF Function.
As aforementioned, using the
COUNTIFfunction is perhaps the best solution to counting cells that are not blank in Google Sheets.
It’s not uncommon to find ourselves committing little errors when typing or inputting data in our spreadsheets. There might be cases where we hit the space bar on a cell, then pressed enter without knowing. Or maybe thought to add a text, added an apostrophe, but you left it that way. In such instances, using the
COUNTIF function will definitely accurately provide us the right count for cells that are not blank.
I’ll give you an example below.
When you typed in an apostrophe and left it that way, it won’t actually register on Google Sheets, but it’s there. In other words, it’s “as if” empty.
As you can see in the example above, I typed in a space on cell A4and an apostrophe on cell A11. Instead of having an outcome of 8, it gave me a result of 10. Meaning, the
COUNTA function included the space and the apostrophe in the count.
This is a clear picture of a situation where the
COUNTIFfunction fares well.
We can use the
COUNTIF formula as shown below:
Let’s break this
COUNTIF function down to understand how it works:
- = denotes the start of every formula written in Google Sheets.
COUNTIF()is our function. We need to add other attributes for it to work smoothly.
rangeis the group of cells that the function is to search. For our example, we will be using A2:A15 as the range which contains the cells we want to count.
criterionis the condition where each cell in the range is to be tested whether it is to be true or false. For this we provide the following:
">0"is a comparison operator which means “greater than 0”. In this case, we want to say that the criterion should be that anything that is lesser than 0 should be avoided. You probably learned this from the previous COUNTIF post.
&is called ampersand and it merges the comparison operator and the asterisk.
"*"is called a wildcard character and represents or take the place of any number of characters. This is useful as we need to avoid counting unnecessary characters which are not blank such as, for example, apostrophes, full stops, commas, or even spaces.
After applying the
COUNTIF function, we manage to obtain the correct count of cells which is 8 ignoring the spaces and unwanted characters which appear as blank cells.
⚠️ Now a few notes about the difference between COUNT and COUNTA
COUNT function is commonly used when counting a range of cells that includes numbers and dates. It doesn’t count blank cells.
On the other hand,
COUNTA function counts numbers, texts, dates, characters and even spaces.
⚠️ A few more notes when using these formulas
COUNTAfunction is used when the data is perfect, meaning, with no unnecessary characters like apostrophe and space.
- When using the
COUNTIFfunction, make sure to supply the necessary attributes such as the comparison operator, the ampersand, and the asterisk. Provided so will allow your function to filter out cells that are not blank but appear as blank in your spreadsheet.
3. Using SUMPRODUCT Function.
The third solution to use is to utilize the
SUMPRODUCT function and it works great at counting non-empty cells.
To make use of it we will need to provide it two other extra functions too which are the
TRIM functions. Doing so will allow the
SUMPRODUCT function to ignore cells that may have null string or spacesas well as unwanted characters such as apostrophe being counted.
We can use the
SUMPRODUCT formula as shown below:
Again let’s break this down:
- = required to start the function.
- SUMPRODUCT() works by calculating the sum of products of the entries. In this case, since we only provide one entry which is A2:A15 and not separated by a comma.
- LEN() is responsible to make sure each cell is inspected to see if there is at least one character or number in it. In other words, the length of the characters will have to be >0 (greater than 0). If not, it will be avoided.
- TRIM() makes sure that any spaces are ignored.
- >0 as mentioned, the length of characters will have to be greater than 0 in order to be considered. If not it is not counted and is considered blank.
When combined and implemented, the function will also provide a count of 8 just like
However, even though the
SUMPRODUCTfunction avoids hidden characters and spaces entered in the data from being counted, the one caveat is that it does count numbers and characters such as brackets, apostrophes, commas, etc. which are included as part of the data as well.
In this case, you will need to resort back to using the
COUNTIF function if you really want to avoid such characters from being counted.
How to Count If Not Blank in Google Sheets Using COUNTIF
You can follow along in this section to write your own function to count non-empty cells in Google Sheets. Since
COUNTIF is the more robust out of the three solutions discussed above, we will be using it instead.
We begin as follows:
- First, click on any cell and make it active. For this guide, I will be choosing D5.
- Next, we enter =COUNTIF followed by an opening parenthesis ‘(‘ to begin our function.
- Now for the first attribute which is our range, we will select the enter A2:A15, as this is the cell range that we want to count.
- Next, after entering the comma ‘,‘ we provide the criterion to evaluate the range we had already provided. For this criterion, we enclose in a quote-unquote symbol (“”), we will write greater than zero, or “>0“. Remember this is to count cells that are greater than 0 as we want to say that the cells which are lesser than 0 should be avoided.
- After this, we will add an ampersand ‘&‘ to connect our first criterion with our second criterion. You do not have to enter a comma ‘,‘ here.
- Now we provide the second criterion for which we type in the asterisk ‘*‘. This is to ignore characters that we may accidentally input in the cell range which appear as blank cells.
- Finally, enter the closing parenthesis ‘)‘ and hit the Enter key to obtain the final result.
That’s it. Well done! 👏🏆
You may make a copy of the spreadsheet using the link I have attached below:
Have a feel on how to work with this formula. Try it out for yourself.
That’s pretty much it. You now know how to count if not blank in Google Sheets. Try experimenting it together with the other numerous Google Sheets formulasto create even more powerful formulas that can make your life much easier. 🙂
Get emails from us about Google Sheets.
Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.
To count the number of cells that are not blank, just follow these steps. Select a blank cell and type the =COUNTA function including the range of cells that you want to count. For example, we used =COUNTA(A2:A11). Just hit enter, and the COUNTA function will automatically count the cells that are not blank.How do I use Countif in Google Sheets with multiple criteria? ›
Count in Google Sheets with multiple criteria — AND logic
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) It is normally used when there are values in two ranges that should meet some criteria or whenever you need to get the number falling between a specific range of numbers.
The COUNTIFS function is a premade function in Google Sheets, which counts cells in a range based on one or more true or false condition. It is typed =COUNTIFS : =COUNTIFS(criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...])What is <> in Google Sheets? ›
How Do You Do Does Not Equal in Google Sheets? The symbol used for the Does Not Equal function in Google Sheets is “<>.” This is a comparison function that allows you to check if the value inside the cell is not equal to the value of another cell. The output is then given out as either TRUE or FALSE.How do I count occurrences in a range in Google Sheets? ›
The COUNTIF function in Google Sheets counts the number of cells in a cell range that meet a specified condition. The function is a practical tool when you want to know the number of times a specific criterion is met within a range of cells. You can only test one condition using the COUNTIF function.Can Countifs have 3 criteria in Google Sheets? ›
The first allows you to specify a range of cells and the criterion that cells must meet to be counted. COUNTIFS, however, allows you to specify multiple ranges and multiple criteria.Can Countifs have 3 criteria? ›
You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.How do you count if not blank? ›
The COUNTIF not blank function counts non-blank cells within a range. The universal formula is “COUNTIF(range,”<>”&””)” or “COUNTIF(range,”<>”)”. This formula works with numbers, text, and date values. It also works with the logical operators like “<,” “>,” “=,” and so on.How do you use condition on COUNT? ›
COUNT() with HAVING
The HAVING clause is used instead of WHERE clause with SQL COUNT() function. The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.
- On your computer, open a spreadsheet in Google Sheets.
- Select the cells you want to apply format rules to.
- Click Format. Conditional formatting. ...
- Create a rule. Single color: Under "Format cells if," choose the condition that you want to trigger the rule. ...
- Click Done.
This means that where this formula checks for the given condition, there could be any number of characters/words before and after the criteria. In simple terms, if the word Mark (or whatever your criterion is) is present in the cell, this formula would count the cell.
- Select the range of cells, or make sure the active cell is in a table. ...
- On the Data tab, in the Sort & Filter group, click Advanced. ...
- Click Copy to another location.
- In the Copy to box, enter a cell reference. ...
- Select the Unique records only check box, and click OK.
To count the cells which have text value in them, enter the formula =COUNTIF(range,criteria) in the destination cell. This is the same as the previous case, but adding wildcard “?” together with “*” only counts the cells which have text values.How do I use multiple ifs in Google Sheets? ›
- =IF(ISNUMBER(TIMEVALUE(B2)),IF(B2<E$2,"YES","NO"),"Invalid Data")
- =IF(B2<E$2, "Gold", IF(B2<E$3, "Silver", IF(B2<E$4, "Bronze/Silver", IF(B2<E$5, "Titanium", IF(B2<E$6, "Bronze", IF(B2<E$7, "Copper", IF(B2>=E$7, "NO MEDAL")))))))
- On your computer, open a spreadsheet in Google Sheets.
- Highlight the cells you want to calculate.
- In the bottom right, find Explore. . Next to Explore, you'll see "Sum: total."
- To see more calculations, click Sum. Average. Minimum. Maximum. Count.
While there is no explicit CONTAINS function in Google Sheets, it does not mean that you cannot pull this off! You can combine the IF function with the SEARCH and REGEXMATCH functions to get the same result. Learn how to do it by following this tutorial.How do you count occurrences within a range? ›
Use the COUNTIF function to count how many times a particular value appears in a range of cells. For more information, see COUNTIF function.How do I auto count in Google Sheets? ›
Use autofill to complete a series
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.
In Excel, I can tell you some simple formulas to quickly count the occurrences of a word in a column. Select a cell next to the list you want to count the occurrence of a word, and then type this formula =COUNTIF(A2:A12,"Judy") into it, then press Enter, and you can get the number of appearances of this word.What is the difference between Countifs and Countif? ›
The difference between COUNTIF and COUNTIFS is that COUNTIF is designed for counting cells with a single condition in one range, whereas COUNTIFS can evaluate different criteria in the same or different ranges. When doing financial analysis, COUNTIF helps in doing a quick analysis.
The COUNTIFS function can be used to count the number of unique values in a range of cells that match a specific set of criteria. The COUNT function can be used to count the number of unique values in a range of cells. The DISTINCT function can be used to count the number of unique values in a range of cells.Can you have multiple criteria in a Countif? ›
Using COUNTIF with multiple criteria. The best function you can use for situations when you need to count cells based on multiple criteria is the COUNTIFS function. The “s” on the end of Countif makes it plural, and that means that there are multiple criteria.How do I find data with 3 criteria in Excel? ›
- The INDEX function can return a value from a specific place in a list.
- The MATCH function can find the location of an item in a list.
In Google Sheets, the COUNTIF function accepts only one data range and one criterion.Can you have 3 if statements in Excel? ›
While Excel will allow you to nest up to 64 different IF functions, it's not at all advisable to do so.How do you count non blank cells excluding formulas? ›
COUNTA to Count Non-Empty Cells
In Excel, you can use COUNTA (that you can use to a number of cells from a range that ate not empty). In the following examples, we have used the same range, A1:A10, but here we have used the COUNTA function.
Using COUNTIF and COUNTIFS
While COUNTBLANK returns the number of blank cells, you can also use COUNTIF or COUNTIFS to achieve the same result.
Count() is a Python built-in function that returns the number of times an object appears in a list. The count() method is one of Python's built-in functions. It returns the number of times a given value occurs in a string or a list, as the name implies.What does count (*) do in SQL? ›
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows.What does SELECT count 1 do? ›
The COUNT(1) function replaces all records from the query result set with value 1. If you have NULL values, it is also replaced by 1. Therefore, COUNT(1) also returns the total number of records (including NULLs) in the table.
If you want to highlight duplicate cells or entire rows containing duplicate entries, you can create conditional formatting rules based on the COUNTIF formulas, as demonstrated in this tutorial - Excel conditional formatting formulas to highlight duplicates.How do I count if cells contain text in Google Sheets? ›
- Navigate to docs.google.com/spreadsheets/d/1rJcrXLOw_fQhJ6e... ...
- Type =COUNTA(ENTER YOUR DATA RANGE HERE) In this example, the data range was A2:A39 or you can just highlight all of the cells you need to count. ...
- Close your parentheses by typing ") ENTER"
You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.How do you count cells if another cell meets criteria? ›
- Select the cell in which you want to see the total.
- Type an equal sign (=) to start the formula.
- Type: COUNTIFS(
- Select the cells that contain the values to check for the first criterion. ...
- Type a comma, and the first criterion: "Pen"
The COUNT DISTINCT function returns the number of unique values in the column or expression, as the following example shows. SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL.What is the formula for not blank in Google Sheets? ›
You can use the ISBLANK formula as the logical test in an IF function to perform different actions depending on whether a cell is empty or not. For example, =IF(ISBLANK(A1), "Empty", "Not Empty"). IFERROR: The IFERROR function allows you to return a custom value if a formula results in an error.How do I ignore blank cells in an array formula in Google Sheets? ›
You can try this formula =ARRAYFORMULA(IF(ISBLANK(A1:A),"",(A1:A + B1:B))) if this works out for you.How do you conditional formatting if a cell is not empty in Google Sheets? ›
Conditional Formatting Based on Another Cell Not Empty
Click on 'Format' in the navigation bar, then select 'Conditional Formatting.' Under 'Format Rules,' select 'Custom formula is.' Write your formula using the following format: =NOT(ISBLANK([cell#)), select your formatting style, then click 'Done.'
To count the cells with text in Excel, choose a destination cell and enter the formula =COUNTIF(range,criteria). Here, the range denotes the array of cells within which you want the function to act. The criteria variable denotes the condition to satisfy when counting the values.How to find the last non blank cell in a column in Google Sheets? ›
- =ArrayFormula(LOOKUP(2,1/(A:A<>""),A:A)) // ignore errors.
- =ArrayFormula(LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) // doesn't ignore errors.
- =ArrayFormula(INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))) // doesn't ignore errors.
Using the empty text string method will return TRUE (therefore ignore the formula) if the cell is empty or contains an empty text string. Using the ISBLANK method will return TRUE (therefore ignore the formula) only if the cell is empty.How do you exclude blanks from an array? ›
Found a simpler and easier way, basically Excel considers all blank cells ( "" ) equal, so another easy way is to select the column that contains the data and click on Remove Duplicates under the Data tab, this will remove (skip) all of the blank empty cells.How do I ignore blank cells in a list? ›
Select the cells where the data validation is set, and then in the Ribbon, select Data > Data Tools > Data Validation. This option is available with any validation criteria be it whole number, decimal, list, date, time, text length or a custom format. Make sure Ignore Blank is checked and then click OK.How to check if a cell has conditional formatting in Google Sheets? ›
- On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
- Tap Format. Conditional formatting.
- At the top, choose an option: Selected range: Shows the rules that intersect with the selected range. All: Shows all rules on the sheet.
- To return to your spreadsheet, tap Done .