Exceljet

Quick, clean, and to the point

Only calculate if not blank

Excel formula: Only calculate if not blank
Generic formula 
=IF(criteria,formula(),"")
Explanation 

To run a formula only when one or more cells are not blank, you can use the IF function with an appropriate logical criteria. In the example shown, the formula in E5 is:

=IF(COUNT(C5:C7)=3,SUM(C5:C7),"")

Since C7 has no value in the screen above, the formula shows no result. In the screen below, C7 contains a number and the sum is displayed:

Same formula with calculation

How this formula works

The goal of this example is to verify input before calculating a result.  The key point to understand is that any valid formula can be substituted. The SUM function is used only as an example. The logic can also be adjusted in many ways to suit the situation.

In the example shown, we are using the IF function together with the COUNT function. The criteria is an expression based on the COUNT function, which only counts numeric values:

COUNT(C5:C7)=3 // returns TRUE or FALSE

As long as the range contains three numbers (i.e. all 3 cells are not blank) the result is TRUE and IF will run the SUM function. If not, result is FALSE and IF returns an empty string ("").  Since C7 has no value in the screen above, the formula shows no result. 

There are many ways to check for blank cells, and several options are explained below.

With 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 number except zero, the IF function will evaluate as TRUE, and return nothing (""). If COUNTBLANK returns zero, IF evaluates as FALSE and returns the sum.

With ISBLANK

In the example shown, input cells are all in the same contiguous range. In cases where cells are not together, you can a formula like this:

=IF(OR(ISBLANK(C5),ISBLANK(C6),ISBLANK(C7)),"",SUM(C5:C7))

This example takes a literal approach with the ISBLANK function.  Because we want to check all three cells at the same time, we need to use ISBLANK three times inside the OR function. This is the logical test inside IF:

When OR returns TRUE (at least one cell is empty), IF returns an empty string (""). When OR returns FALSE (no cells are blank), IF runs the SUM function and returns the result:

SUM(C5:C7)

With logical operators

The ISBLANK function can be replaced with standard logical operators like this:

=IF(OR(C5="",C6="",C7=""),"",SUM(C5:C7))

Alternately, we can combine the not equal to operator (<>) with AND function like this:

=IF(AND(C5<>"",C6<>"",C7<>""),SUM(C5:C7),"")

Notice the SUM function has been moved to the TRUE result. It will run only if C5 and C6 and C5 are not empty.

With COUNTA

Finally, you can use the COUNTA function to check for numeric or text input:

=IF(COUNTA(C5:C7)=3,SUM(C5:C7),"")

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.