I am trying to get my Totals Column (column G/rows 4:16), to display "$ -" when the cells in Columns A:F are blank. Then Column G to display "$ 0.00" if I put a 0 (Zero) in any of cells in Columns A:F. I currently have the "Sum(A:F)" formula in column G.
2 Answers
Use this Formula in G4:
=IF(AND(A4="",B4="",C4="",D4="",E4="",F4=""),"$-",IF(AND(A4=0,B4=0,C4=0,D4=0,E4=0,F4=0),"$0.00",SUM(A4:F4)))
you can drag it to G16
Update for format Sum
To insert a custom format to the Sum you can use the following:
=IF(AND(A4="",B4="",C4="",D4="",E4="",F4=""),"$-",IF(AND(A4=0,B4=0,C4=0,D4=0,E4=0,F4=0),"$0.00",TEXT(SUM(A4:F4),"$#,##0.00 ;($#,##0.00)")))
But the result is considered as Text you have to use Value() to calculate it when needed
-
I had custom formatting in the cell(s) in column G, however the suggested formula over rides the custom formats. Any Suggestions on this matter? (example: $* #,###.00_);[Red] (-$* #,###.00);_( $* " - "???_);_(@_)– I JosephMar 9, 2017 at 23:13
-
-
1Try formatting cells Number, Custom (you can compromise between format number ("$-" is consider as text)– yassMar 10, 2017 at 13:26
-
1
-
Awesome, Thank You... That definitely helped and now my spreadsheet is doing what I want it to do and looking the way I want it to. Thanks again.– I JosephMar 10, 2017 at 14:22
Where the potential for trouble is with this, you're checking for two conditions. Whether the entire of A:F is blank and if any of them contain a zero.
This will require some careful use of the words AND and OR inside your IF statement which allows for multiple tests and will simplify a little.
All IF statements use the same formula: =IF(, , )
Start with =IF(AND(A4="", B4="", {etc until you get to F4}), "$ -", )
The above code checks to see if all of A4 to F4 are blank and enter the "$ -" you require and leaves the second part to check for zeroes.
This is OR(A4=0, B4=0, {etc until you get to F4}), 0, SUM(A4:F4))
Once the line of code is complete, you can then copy and past it for the rest of column G.
