Need a cell to be BLANK - NO ZEROES - if no data is input into the FORMULA RANGE above?

Question

I'm trying to make it so that a column of data sum when data is input into the column above, but remains BLANK if there's no data.

I've been able to accomplish this with AVERAGING, using this formula below:
=IFERROR(AVERAGE(AU20:AU69),"") This is obviously possibly because of the AVERAGE error that occurs.

Currently, this is the formula I'm working with, re: finding the SUM of the column:

=IF(AU26:AU69="","",SUMIF(AU26:AU69))

I've also tried variations of this, such as:

=IF(SUM(AU26:AU69)<>"",SUM(AU26:AU69),"")

Both of these scenarios keep showing 0 instead of leaving the cell blank.

I'm wondering if this is due to the input of a range, rather than individual numbers?

I've tried conditional formatting and creating a custom number format, and while both technically work, I want to find a way to make this happen using a formula, as it's a far more elegant solution and would cause less headaches if the sheet format ever changes/is customized.

Answer 1

You can do that with if(), like this:

=if( sum(AU26:AU69), sum(AU26:AU69), iferror(1/0) )

...or with iferror():

=iferror( 1 / sum(AU26:AU69) ^ -1 )

The iferror(1/0) pattern is used to get a truly empty value in cells you want to leave blank. It is almost the same as the zero-length text string "", but such zero-length text strings often cause trouble when you use functions like counta() that will count the "" value even though the cell appears blank. The same way, isblank() will treat truly empty values differently from zero-length text strings.