#Value error

[Jay]

Hi guys:

I am attempting to roll the value of two cell into (AH36 and AH37) one by
adding the two values together if the cell C1 has an "X" in it. This is
partially what I have thus far:
=SUM((C36:AG36)+IF(C1="x",C37:AG37),0).

I would also like to set the value of cell AH37 (which contains the Sum
C37:AG37) after the values have been added together in AH36 to 0. is this
possible? How can this be done?

Thanks,......

 

[Bob Umlas]

=SUM((C36:AG36)+IF(C1="x",SUM(C37:AG37)),0)
2nd part would have to be manual or with VBA

Bob Umlas
Excel MVP

 

[hgrove]

Jay wrote...

I am attempting to roll the value of two cell into (AH36 and
AH37) one by adding the two values together if the cell C1 has
an "X" in it. This is partially what I have thus far:

=SUM((C36:AG36)+IF(C1="x",C37:AG37),0)

Unless you enter this as an array formula, this should return #VALUE!
It's due to the IF funtion. If you only want to include cells i
C37:AG37 when cell C1 contains "X", try either

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

or

=SUM(OFFSET(C36:AG36,0,0,IF(C1="X",2,1))

 

[PatrickC]

=SUM(SUM(C36:G36)+IF(C1="X",SUM(C37:G37),0))

-----Original Message-----
Bob:

Thanks for the assistance, however, I'm still getting the #Value error using
the suggested solution. Is there something else wrong?


.