What Is COUNTIF Not Blank In Google Sheets?
The COUNTIF Not Blank in Google Sheets counts only the cells that consists of cell values regardless of whether it is numeric, text, alpha-numeric or symbols, based on the criteria that the blank or empty cells are not counted. Users can utilize the Google Sheets COUNTIF not blank to find the count of the financial and accounting statements based on non-blank cells.
For example, the dataset below contains a few items purchased and their quantities. We will find the count of only the items that are found or purchased using the COUNTIF() function.
Select cell E1, enter the formula =COUNTIF(B2:B9,”<>”) and press “Enter”, as shown below.
We can see that the count of items purchased is 4, but we have 6 items in the list. It is due to the operator ‘<>’ that interprets as not equal to blank and helps the COUNTIF not blank formula to count only the values of the items present.
Key Takeaways
- The COUNTIF Not Blank in Google Sheets formula counts the cell values regardless of the values, whether they are numeric, text, symbols, special characters, etc. numbers in a range based on the non-blank cells criteria applied.
- The formula accepts two mandatory arguments, range and criteria (the criteria argument value is typically the ‘<>’ operator) which can be inserted along with other data, so that we can filter those and return the rest of the count.
- We can use the ARRAYFORMULA along with the COUNTIF when we have an array as a dataset. However, there is no difference when we use the ARRAYFORMULA function or execute the formula as an array using the “Ctrl+Shift+Enter” for COUNTIF, because we have only one criterion.
Syntax
The syntax of the COUNTIF formula in google sheets is,
The mandatory arguments of the COUNTIF formula in Google Sheets are,
- range: It is the selected cell range from the dataset to find the count of cells that satisfy the given criteria.
- criterion: It decides the cells to count and the ones to be ignored, here, if the cells are not blank, i.e., value as “<>”. The argument value can be a numeric or non-numeric values.
How To Use COUNTIF Not Blank In Google Sheets?
We can use the Google Sheets COUNTIF Not Blank in the following two methods, namely,
- Access from the ribbon.
- Enter into the worksheet manually.
Method #1 – Access from the ribbon –
Step 1: Choose an empty cell for output- select the “Insert” tab – click the “Function” option right arrow – click the “Math” option right arrow – select the “COUNTIF” function, as shown below.
Now, the COUNTIF formula appears as shown below.
Step 2: Select the range argument as a cell range and the criterion value as “<>” and press “Enter”.
Method #2 – Enter in the worksheet manually –
Step 1: First, select an empty cell to display the output.
Step 2: Type =COUNTIF( in the chosen cell. [Alternatively, enter =C or =COUNT and click the COUNTIF function from the suggestions Google Sheets lists.]
Step 3: Enter the cell range as the argument value. Ensure to supply the ‘<>’ operator in double quotes if supplying it directly as the criteria argument value.
Step 4: Close the brackets and press Enter to obtain the count of the non-blank cells.
Examples
We will consider some COUNTIF not blank in Google Sheets examples to understand the function better.
Example #1 – Count tasks marked as ‘Completed’
The dataset below shows the corrections of subjects that are completed and pending in an educational institution. We will Count tasks marked as ‘Completed’ using the COUNTIF not blank in Google Sheets formula.
The steps to Count tasks marked as ‘Completed’ are,
Step 1: Select cell G1 and enter the formula =COUNTIF(D2:D11,”<>Not Completed”), as shown below.
Step 2: Now, press the “Enter” key to get the tasks completed count, as shown below.
The function looks for the “non-blank not completed” cell values in the range D2:D11, based on the specified criterion, ‘<>’ and returns the value as 8.
Example #2 – Count students ‘Present’ on a Particular day
Consider the table given below that consists of 15 students’ attendance data. We will Count students ‘Present’ on a particular day using the COUNTIF not blank in google sheets formula.
The steps to Count students ‘Present’ on a particular day are,
Step 1: Select cell H1 and enter the formula =COUNTIF(E2:E16,”<>Absent”), as shown below.
Step 2: Now, press the “Enter” key to get the count of Students present, as shown below.
The function looks for the “non-blank absent” cell values in the range E2:E16, based on the specified criterion, ‘<>’ and returns the value of 13 as 2 students are absent out of 15.
Example #3 – Count the products marked as ‘Sold’
The dataset given below is a firm’s monthly sales generated for the electronic items sold and the total of the sales in column C. We will Count the products marked as ‘Sold’ using the COUNTIF not blank in google sheets formula.
The steps to Count the products marked as ‘Sold’ are,
Step 1: Select cell F1 and enter the formula =COUNTIF(B2:B13,”<>Not Sold”), as shown below.
Step 2: Next, press the “Enter” key to get the count of items that are sold, as shown below.
The function looks for the “non-blank not sold” cell values in the range B2:B13, based on the specified criterion, ‘<>’ and returns the value of 9.
Example #4 – Uisng ARRAYFORMULA
The following dataset contains a list of smartphones and their respective quarterly sales for the months of January to March. Hence, we will use the ARRAYFORMULA() along with the COUNTIF() function to find the count of the non-blank cells.
The steps to count the non-blank cells using the COUNTIF and the ARRAYFORMULA are,
Step 1: Select cell A10 and enter the formula =ARRAYFORMULA(COUNTIF(A1:D7,”<>”)), as shown below.
Step 2: Press the “Enter” key to get the count of non-blank cells, as shown below.
The function looks for the “non-blank” cell values in the range B2:B13, based on the specified criterion, ‘<>’ and returns the value of 22. Remember we have selected the entire dataset from A1:D7 that consists of column headers that are text, numeric values, text values, etc. And the formula has ignored the blank cells an returned the rest of the non-blank cells regardless of the cell values.
Important Things To Note
- If we supply the ‘<>’ operator directly as the COUNTIF not blank formula argument value criteria, it should be within double quotations. Otherwise, the formula will not work.
- The values in the range, if specified, in the COUNTIF non-empty formula can be any cell value, because we want only the count and no arithmetic operations are performed.
- The COUNIF() function is from the group of COUNT functions that also has COUNTIFS, COUNTA, COUNTBLANK, COUNTUNIQUE, COUNTUNIQUEIFS, that returns the count of all the required values that satisfy the specific criteria.
- Ensure to provide both the mandatory arguments, because if we do not provide inputs to one or both the arguments, then we will get the “#NA” error.
Frequently Asked Questions (FAQs)
There are times when we cannot remember in which category the “COUNTIF” function falls. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “COUNTIF” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
A few reasons the Google Sheets COUNTIF not blank may not work are,
• The range values selected does not have blank or empty cells.
• The ‘<>’ operator supplied directly as the criteria argument value to the COUNTIF() is not in double-quotes.
• One or both the argument values are not provided.
The difference between COUNTIF not blank and COUNTIFS not blank is that the COUNTIF not blank formula in counts the values based on one criterion, which is non-blank cells.
On the other hand, the COUNTIFS not blank formula in also counts values based on non-blank cells. However, we can include multiple conditions to check simultaneously, along with non-blank cells, to count the required values.
Alternatively, we can find the Functions icon to insert the Google Sheets COUNTIF by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “COUNTIF” function, as shown below.
Download Template
This article must help understand COUNTIF Not Blank in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is COUNTIF Not Blank In Google Sheets. We learn its syntax & how to use it to count non-blank cells with detailed examples. You can learn more from the following articles. –
Leave a Reply