Run formula only If cell contains a value

Question

I'm using Microsoft Office 365. I'd like to do something like

=(SUM(J4:J20)+K23)/G23

but sometimes K23 doesn't have a value. So I'm thinking I need to say only do +K23 if it contains a value.

How would I do that?

Solution 1

Try using

=SUM(J4:J20,IF(K23<>"",K23))/G23

Unless sometimes G23 is blank or zero, replace /G23 with /if(G23<>"",G23,1)

Solution 2

There's no need to use any IF conditions here. Just include K23 in the SUM function. SUM will ignore any non-numeric, non-error values, so it doesn't matter if K23 is blank.

 =SUM(J4:J20,K23)/G23

Solution 3

With Office 365 you could use IFError() like

=SUM(J4:J20)+IFERROR(K23/G23,0)

ISError() works differently and the earlier suggestion will present an error. You can still use ISError(), but it would need to be nested in an IF() statement:

=SUM(J4:J20)+IF(ISERROR(K23/G23),0,K23/G23)

This will mean that the division is carried out twice, which is slower than the IFError(), where it is only calculated once.

Comments

[David Custer]

@slhck Thank you for showing me how to better ask my question.

[Raystafarian]

Do you want the division by G23 to apply to the sum of J4:J20 if K23 has no value? I know that's not what the formula says, just checking

[David Custer]

@Raystafarian Yes

[Raystafarian]

You should change your requested formula to indicate that like =(sum(J:J)+K)/G)

[David Custer]

I'm looking for (J4:J20) which is $17.20 + $23.70 + $78.00 then plus K23, if there is a value (which in this case is $20), then divide it all by G23 which is 130.... So using my calculator that should be $1.07. And the formula your giving me comes up with $119.05?

[David Custer]

Right now I'm using =SUM(J4:J20,IF(K23<>"",K23))/if(G23<>"",G23,1) ... Is it possible to say if G23 is blank or 0 then abort/don't do anything/exit/don't even do =SUM ??

[teylyn]

Ah, I started with the suggested IsError formula, which had the concept wrong. Never mind.

[Canadian Luke]

This doesn't look like a complete answer... Can you expand on it?

[bryaninthesky]

opps! I had it wrong at first! updated answer should work!!

[Scott - Слава Україні]

(1) This doesn’t really seem to be what the question is asking for.  (2) The question has been adequately answered.  (3) There’s no reason to say SUM(D5); it’s equivalent to D5. Likewise, SUM(D5)+(B6)-(C6) is equivalent to D5+B6-C6.

[Scott - Слава Україні]

This is the correct answer.