Help with a particular SUM formula

[Jay]

Gentlemen:

Here is the problem: =SUM(C37:AG37,IF(C1="X",(AH37=0)))

What I'd like to do is make the value in cell AH37 zero 0 if the cell C1 has
an "X" in it. If C1 does not have an "X", display the actual value result of
the SUM function.

Any assistance in this matter will be greatly appreciated.

Thanks,

 

[Biff]

Hi!

Try this:

=IF(C1="X",0,SUM(C37:AG37))

Biff

 

[Jay]

Thanks, Biff:

It worked nicely!!!

Hi!

Try this:

=IF(C1="X",0,SUM(C37:AG37))

Biff

 

[Frank Kabel]

Hi
try:
=SUM(C37:AG37)*(C1<>"X")

 

[David]

Formula in cell AH37: =IF(C1="X",0,SUM(C37:AG37))

Hope that helps

David

 

[JT]

Hello all,

I've recreated both formulas and I'm just trying to figure out how and why
Frank's formula works. What returns the zero if c1=X? I'm guessing it has to
do with the true/false component. This is one great forum!
Thanks.

John

 

[Frank Kabel]

Hi
normally it returns TRUE/FALSE. But as this expression is used in a
mathematical operation Excel coerces the boolean value to 1/0. So if
this equation returns False, Excel coerces this to '0' and therefore
the complete expression equals zero