How to hide the function cell when there is no value in the referenced cell

  • Thread starter Thread starter jim
  • Start date Start date
J

jim

I have a sheet with a number of columns with a "total" row at the bottom.
If there are no values to sum then the total cell displays a "0". Is it
possible to have the cells in the "total" row not display anything until a
value is entered in the respecive referenced cells?

Thanks,

Jim
 
Jim,

Try something like

=IF(SUM(A1:A100)=0,"",SUM(A1:A100))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
jim

Put the sum in an IF statement.
If it was =SUM(A2:A25), replace that with =IF(SUM(A2:A25)<>0,SUM(A2:A25),"")

Andy
 
This will hide sums of zero in situations where values of, say,
-100, 20 and 80 are entered in E1:E3. A more robust way:

=IF(COUNT(E1:E10),SUM(E1:E10,"")
 
Good point!

Dan E

J.E. McGimpsey said:
This will hide sums of zero in situations where values of, say,
-100, 20 and 80 are entered in E1:E3. A more robust way:

=IF(COUNT(E1:E10),SUM(E1:E10,"")
Click to expand...
 
I tried:

=IF(ABS(SUM(F22F29))>0,SUM(F22:F29),"")

and got #NAME?

and with:

=IF(ABS(SUM(F22F29))>0,SUM(F22:F29),"")

I got #VALUE?

Is there something I've got wrong?

Thanks, Jim
 
Back
Top