if function

[Louise]

I have a formula that is not working.
=if($Z$58="",ag46+ag48+ag50)-if($z$58="X",0) If cell z58 is empty I want it
to add the ag cells and if z58 cell has an x in it I want it to show 0. It
is doing 0 for both functions, it is not adding the ag cells. What am I
doing wrong?

 

[John C]

Just curious, what do you want if $Z$58 is not blank and not an x?
=IF($Z$58="",SUM(AG46,AG48,AG50),IF($Z$58="x",0,"Z58 is neither equal to x
or blank")

 

[google]

You put:
=if($Z$58="",ag46+ag48+ag50)-if($z$58="X",0)

I believe it should be:
=if($Z$58="",ag46+ag48+ag50,if($z$58="X",0))

if I'm reading your explanation right.

BTW, consider using CHAR(215) instead of "X". It's the 'times' sign
and I think it looks a bit better (IMHO). e.g. "×"

 

[Louise]

That is the only 2 things I want in z58 blank or x

 

[John C]

In that case, you could use data validation, go to menu Data-->Validation,
choose Custom for the Allow field
And either type or copy the following formula into the formula field:
=AND(LEN(Z58)=1,OR(CODE(Z58)=88,CODE(Z58)=120))
You can put in information if you want on the Input Message tab, or even
customize your error if they try to make an invalid entry into the cell on
the Error Alert tab.
Then your formula would be:
=($Z$58="x")*(SUM(AG46,AG48,AG50)