If any value in the array is TRUE, the OR function returns TRUE, causing IF to return an empty string (''). In this formula, we pass a range into ISBLANK and ISBLANK returns an array that contains 3 results. In the current version of Excel, it will work fine as-is. Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and older. 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)) OR will return TRUE if any supplied value is TRUE. 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)) ISBLANK returns TRUE when a cell reference is empty. IF + ISBLANKĪnother approach is to use the ISBLANK function. If COUNTBLANK returns zero, IF will evaluate zero as FALSE and return the result from the SUM function. If COUNTBLANK returns any non-zero number, the IF function will evaluate the number as TRUE, and return an empty string (''). 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))

There are many ways to check for blank cells, and several other options are explained below. 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. 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. This will cause the IF function to return the value_if_false, which has been supplied as an empty string (''). This test will return FALSE until the range C5:C7 contains three numbers. The logical test is based on the COUNT function, which counts numeric values: COUNT(C5:C7)=3 // returns TRUE or FALSE Translation: if the count of numbers in C5:C7 is 3, sum the range C5:C7. In the example shown, we are using the IF function together with the COUNT function: =IF(COUNT(C5:C7)=3,SUM(C5:C7),'') The best solution depends on the requirements of the project and your personal preference. Below are several examples of how you can approach this problem. The logic can be adjusted in many ways to suit the situation. You can use the same approach with any formula to prevent calculation until all required values are available. In the worksheet shown, the SUM function is used as an example only. The goal is to verify the input of all required values before calculating a result.

Running subtraction formula in excel