0

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.

0

1 Answer 1

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.

1
  • The question was bumped by an answer that now is deleted. Google Sheets allows empty parameters in functions like IF, so the formula might shortened to=if( sum(AU26:AU69), sum(AU26:AU69), ). However, this might not be a good solution if it's required that the spreadsheet be compatible with other apps.
    – Rubén
    Sep 21 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.