Explanation
The goal is to verify the input of all required values before calculating a result. In the worksheet shown, the SUM function is used as an example only. You can use the same approach with any formula to prevent calculation until all required values are available. The logic can be adjusted in many ways to suit the situation. Below are several examples of how you can approach this problem. The best solution depends on the requirements of the project and your personal preference.
IF + COUNT
In the example shown, we are using the IF function together with the COUNT function :
=IF(COUNT(C5:C7)=3,SUM(C5:C7),"")
Translation: if the count of numbers in C5:C7 is 3, sum the range C5:C7. Otherwise, display nothing.
The logical test is based on the COUNT function, which counts numeric values:
COUNT(C5:C7)=3 // returns TRUE or FALSE
This test will return FALSE until the range C5:C7 contains three numbers. This will cause the IF function to return the value_if_false , which has been supplied as an empty string (""). In Excel, an empty string will look like an empty cell. Since C7 has no value in the original worksheet, the formula displays no result. Once the range C5:C7 contains three numbers, the test will return TRUE and IF will run the SUM function, which will return the sum of C5:C7 as a final result.
There are many ways to check for blank cells, and several other options are explained below.
IF + COUNTBLANK
The COUNTBLANK function counts empty cells in a range , so we can write a slightly more compact formula like this:
=IF(COUNTBLANK(C5:C7),"",SUM(C5:C7))
If COUNTBLANK returns any non-zero number, the IF function will evaluate the number as TRUE, and return an empty string (""). If COUNTBLANK returns zero, IF will evaluate zero as FALSE and return the result from the SUM function.
IF + ISBLANK
Another approach is to use the ISBLANK function. ISBLANK returns TRUE when a cell reference is empty. ISBLANK was originally designed to test one cell only, but you can use ISBLANK three times inside the OR function together like this:
=IF(OR(ISBLANK(C5),ISBLANK(C6),ISBLANK(C7)),"",SUM(C5:C7))
OR will return TRUE if any supplied value is TRUE. In other words, the OR function will return TRUE if any of the ISBLANK functions return TRUE. Alternatively, can use a formula like this:
=IF(OR(ISBLANK(C5:C7),"",SUM(C5:C7))
Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and older. In the current version of Excel, it will work fine as-is.
In this formula, we pass a range into ISBLANK and ISBLANK returns an array that contains 3 results. If any value in the array is TRUE, the OR function returns TRUE, causing IF to return an empty string (""). If all values in the array are FALSE (i.e. all three cells contain values), OR returns FALSE, and IF returns the result from SUM:
SUM(C5:C7)
IF with logical operators
Another option is to use standard logical operators like ="" and <>"" to test for empty and non-empty cells. To test for any empty cells, use a formula like this:
=IF(OR(C5="",C6="",C7=""),"",SUM(C5:C7))
To test for non-empty cells use <>"" inside the AND function like this:
=IF(AND(C5<>"",C6<>"",C7<>""),SUM(C5:C7),"")
In this formula, notice the SUM function has been moved to the value_if_true argument, and will only run if all 3 cells are not empty .
IF + COUNTA
Finally, you also use the COUNTA function to test for non-empty cells like this:
=IF(COUNTA(C5:C7)=3,SUM(C5:C7),"")
While the COUNT function only counts numeric values, the COUNTA function counts any kind of value (i.e. numbers or text). As long as the range C5:C5 contains three values (numbers or text), the result will be TRUE and the SUM function will run. This doesn’t really make sense for the example shown (which requires numeric input) but it can be used in other situations.
Explanation
The goal is to display a blank cell based on a specific condition. In the worksheet shown, we want to return the value from column C, but only when the value in column B is “A”. If the value in column B is anything else, we want to display nothing. The easiest way to solve this problem is with the IF function and an empty string ("").
IF function
The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. For example, if cell A1 contains “Red”, then:
=IF(A1="red",TRUE) // returns TRUE
=IF(A1="blue",TRUE) // returns FALSE
Notice the IF function automatically returns FALSE even though no value is provided for a false result. It is important to understand that the IF function is not case-sensitive. If cell A1 contains “Red”, then:
=IF(A1="red",TRUE) // returns TRUE
=IF(A1="RED",TRUE) // returns TRUE
=IF(A1="Red",TRUE) // returns TRUE
Notice that text values inside IF must be enclosed in double quotes (""). However, numeric values should not appear in quotes. For example, if cell A1 contains 100, then do not use quotes to test for 100:
=IF(A1=100,TRUE) // returns TRUE
Enclosing a number in quotes (“100”) causes Excel to interpret the value as text , which will cause the logical test to fail:
=IF(A1="100",TRUE) // returns FALSE
For more details about IF, see: How to use the IF function .
Empty strings in Excel
When the goal is to display nothing with a formula in Excel use two double quotes like this “”. This is called an empty string ("") and it will display like an empty or blank cell on the worksheet. Note if you type "" directly into a cell in Excel, you will see the double quote characters. However, when you enter the quotes as a formula like this:
=""
You won’t see anything, the cell will look empty.
IF with empty string
In the example shown, the formula in E5 is:
=IF(B5="a",C5,"") // returns 82
In this formula, the logical_test is B5=“a”, the value_if_true is C5, and the value_if_false is an empty string (""). As the formula is copied down, the IF function returns the value in column C when the value in column B is “A”. If B5 contains any other value , IF returns an empty string ("") which looks like an empty cell in Excel. Although we are using a lowercase “a”, an uppercase “A” produce the same result:
=IF(B5="A",C5,"") // returns 82
Testing for blank cells
There are many ways to check for blank cells in Excel, see this article for several options . If you need to check the result of a formula that returns an empty string (""), be aware that the ISBLANK function will return FALSE when checking a formula that returns "" as a final result. In other words, while you would expect ISBLANK to return TRUE, it actually returns FALSE. For example, if cell A1 contains a formula that returns an empty string, then:
=ISBLANK(A1) // returns FALSE
One workaround is to use the COUNTBLANK function instead like this:
=COUNTBLANK(A1) // returns 1
=COUNTBLANK(A1)>0 // returns TRUE
You can use COUNTBLANK inside the IF function like this:
=IF(COUNTBLANK(A1)>0,true_result,false_result)
For more details on COUNTBLANK, see: How to use the COUNTBLANK function .
