Show blank if no value in previous cells and show an accounting zero if zero entered in previous cells

[SaraWitch]

Hello peeps,

I have a conditionally formatted formula in G1 to be blank if A1:F1 calculates zero. Conditional format:

_-£* #,##0.00_-;-£* #,##0.00_-;;_-@_-

However, what I really need is for G1 to be blank if A1:F1 are blank and an accounting zero in G1 if zeros are entered in A1:F1 (and, of course, calculate if greater than zero). I have tried several IF formulas in G1, but nothing is hitting the mark.

Any help would be gratefully received

 

[felixstraube]

Could you show us a sample of your data, and the expected results?

 

[SaraWitch]

So, in column G, I want a blank if no values are entered in A:F (e.g., G3) and £0.00 to show if £0.00 manually entered in A:F (e.g., G1) and calculate if anything greater than zero (e.g., G2):

Auto move row.xlsm
	A	B	C	D	E	F	G
1	£ 0.00	£ 0.00	£ 0.00	£ 0.00	£ 0.00	£ 0.00
2	£ 25.00	£ 0.00	£ 0.00	£ 0.00	£ 0.00	£ 10.00	£ 35.00
3
4
5
6
7
8
Sheet3

Cell Formulas
Range	Formula
G1:G8	G1	=SUM(A1:F1)

Cells with Data Validation
Cell	Allow	Criteria
A1:G8	Any value

Hope this helps...

 

[felixstraube]

I could not be done by formating alone. Because sum of nothing (all blank) returns 0, not "" (empty string). So you will have to add this to your formula like so:

Libro2
	A	B	C	D	E	F	G	H
1								<-- no data entered
2	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €	<-- all zero
3	25,00 €	0,00 €	0,00 €	0,00 €	0,00 €	10,00 €	35,00 €	<-- some value
Hoja1

Cell Formulas
Range	Formula
G1:G3	G1	=IF(SUM((A1:F1<>"")*1),SUM(A1:F1),"")

 

[SaraWitch]

Perfect; just what I need! I just tweaked the CF to

_-£* #,##0.00_-;-£* #,##0.00_-;_-£* "0.00"??_-;_-@_-

Thank you, @felixstraube!

 

[felixstraube]

Happy to help. Thanks for the feedback!