How to Do Countif in Excel Not Blank in 5 Easy Ways

When it comes to working with Excel, one of the most common tasks is counting cells. You can use Countif function for cells that are not empty or blank. Excel default function COUNTIF can do this for you. So, how to do countif in excel?

1. How to Use COUNTIF Non-Blank Function?

The COUNTIF as a default Excel function counts the number of cells within a range. And they are sure to meet a specific criterion or condition. This function works to count cells having a certain value or text string. But it can count cells that are not empty or blank.

2. Syntax and usage of the COUNTIF function

The syntax of the COUNTIF function is below.

Where range is the cells to count, and criteria is the condition to apply to the cells.

For example, if you wanna count cells in the range A1:A10 and those should contain the text “apple”, you would use the following formula.

3. count non zero cells in excel and  non-blank cells with the COUNTIF

To count non-blank cells in Excel using the COUNTIF function, you can use the criteria "<>"".And this means not equal to an empty string.

For example, if you wish to count the number of non-blank cells in A1:A10, you can use below formula.

This formula will count all cells which are not empty or blank.

4. Using the COUNTA function to count non-blank cells

Another way to count non-blank cells in Excel is to use the COUNTA function.

Where value1, value2, etc. are the values or ranges of cells you wish to count tough.

how to do countif in excel

For example, to count the number of non-blank cells in the range A1:A10, you wcan write and apply below formula.

And this one will count all cells if they are not empty or blank.

5. Using a combination of functions to count non-blank cells

You can also use a combination of functions to count non-blank cells in Excel. For example, you can use the IF function to check if a cell is empty or not. Ad then, you can apply another COUNTIF function to count the non-blank cells. The syntax of the IF function will be something like below.

Where logical_test is the condition that you want to test, value_if_true is the value or formula to return if the condition is true.

For example, if you wish to find number of non-blank cells in the range A1:A10 using a combination of functions, you may copy and paste below formula.

This formula will first check if each cell in the range A1:A10 is empty. Or not using the IF function. And it will return a value of TRUE or FALSE. Then, it will concatenate  values with the criteria "<>""" using the & operator. And last step is to count the cells meeting this condition.

6. Advanced techniques for COUNTIF Not Blank Function

6.1 Using conditional formatting to count wihout na

Another way to count non-blank cells in Excel is to use conditional formatting. You can use conditional formatting to highlight non-blank cells in a range. AFter this step, you can formulate a COUNT function to count those highlighted cells.

1. First, you will select the cells for counting action.
2. Then, you can click on Conditional Formatting button in Home.
3. You will select a New Rule.
4. Then, you should select Use a formula to determine which cells to format option.
5. Now, you can enter the formula =LEN(A1)>0 in the Format values where this formula is true box. This is where A1 is the first cell in you range.
6. You can click on Format button and choose a format to apply to the non-blank cells.
7. Again you click OK to close the Format Cells box.
8. Last one is to click on OK again to close.

The non-blank cells in the range will now be highlighted with the chosen format. To count the highlighted cells, use the following formula:

This formula will count all cells in the range with any text or value. This is including those are highlighted with conditional formatting.

6.2 Using a pivot table to excel formulas count cell is not null

You can also use a pivot table to count non-blank cells in Excel.

1. After selecting cells,  you will find and click Insert on top.
2. Now, you click on PivotTable in the Tables group.
3. Then, you can choose where you want to place the pivot table and click OK.
4. Now, you can drag the field to count to the Values area of the pivot table.
5. This time you need to right-click on Values area and choose Value Field Settings.
6. You can now choose Count as the summary function and click ok.

Now this pivot table will now show the count of non-blank cells.

7. Tips and tricks for working with the COUNTIF function and excel is not blank

• You can use wildcards such as * and ? in the criteria to match partial text or values.
• Also, you can use cell references or named ranges in the range argument for flexible formulas.
• If you want to count cells based on multiple criteria, you can use the COUNTIFS function for multiple conditions.

8. Common errors and how to avoid them for excel if not blank

• Using the wrong syntax or arguments in the formula.
• Including spaces
• Using quotes around the range argument.

• Forgetting to use the ampersand operator (&) to concatenate text and cell references.
• Not usingdouble quotes ("") to enclose text in the criteria argument.
• Using the wrong comparison operator (e.g. using = instead of <> to count non-blank cells).

9. Conclusion on count wihout na

In conclusion, the COUNTIF function can work for counting non-blank cells in Excel. Using this function combined with other Excel functions such as IF statements, conditional formatting and pivot tables, you can easily count non-blank cells in your sheet.