SUM Only if Cell is not Blank

[cdxjones]

I am trying to have the following calculate only if cell M:M is not blank.

=SUM(W192*2+W193*Q3)

The above works, but I need to add a function to only SUM if cell M:M has data.

Thank You

[6StringJazzer]

Try this:

Please Login or Register  to view this content.

[FDibbins]

M:M is not a cell, it is a range, a cell would be M1.

Now, having said that, do you need to test to see if ALL of M is blank, or just a certain cell?

Also, you dont really need to use SUM like that, this should also work...
=W192*2+W193*Q3

[cdxjones]

Thank You, This worked...

[6StringJazzer]

Ford made a good point. I didn't really read the formula. SUM is superfluous in this case (though does not cause an error).

Please Login or Register  to view this content.

[FDibbins]

Thanks 6

[cdxjones]

I thought this was working... However the formula is calculating the first part of the calculation in BOLD below when range M:M is empty.

=IF(COUNTA(M:M)=0,"",W192*2+W193*Q3

Any thoughts?

[FDibbins]

I thought this was working... However the formula is calculating the first part of the calculation in BOLD below when range M:M is empty.

=IF(COUNTA(M:M)=0,"",W192*2+W193*Q3

Any thoughts?

Not possible. Are you sure M is blank? What do/did you have in M?
Keep in mind that even if you have a formula that return "", that cell is not blank/empty

[JohnTopley]

This implies W193 and Q3 are zero AND M:M is not empty i.e.a cell with a space will NOT be "Blank".

Do "Clear Contents" on column M.

[cdxjones]

It is clear, let me clean this up and upload what I am looking at...

[FDibbins]

OK, will wait for your file

[cdxjones]

Uploaded...

Thanks

[JohnTopley]

Column M has data in rows 2 and 3: so I think formula should be ..



=IF(M3="","",$W$192*2+$W$193*O3)

Copy down

Edited to "fix" cells

[cdxjones]

That was it... Did not catch that...

Thank You...

[FDibbins]

M also has - in a bunch of cells, so they are not blank either.

We come back to what I was getting to in post #3

[6StringJazzer]

Column M has data in rows 2 and 3: so I think formula should be ..



=IF(M3="","",$W$192*2+$W$193*O3)

Copy down

Edited to "fix" cells

Nice recovery. So it wasn't M:M to begin with....