# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

How do you get excel to ignore a formula until cell contains a value i.e.(if B1 is 1000, don't subtract C1 from B1 for a sum in C2 (resulting in -1000), until a value is first put in C1. ANOTHER EXAMPLE Don't add the sum of D5 and D6 in D7 if D5 and D6 are blank, preventing D7 (and E7 and G7...) from showing up as 0.00 ). I would like totals to remain blank and not the whole row showing 0.00 until a number is placed to complete the formula
Solved by V. J. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 12/02/2018 - 02:30
Hello!
Excelchat Expert 12/02/2018 - 02:30
Welcome to Excel chat.
Excelchat Expert 12/02/2018 - 02:30
User 12/02/2018 - 02:30
hello
User 12/02/2018 - 02:31
can you see my question?
User 12/02/2018 - 02:32
How do you get excel to ignore a formula until cell contains a value i.e.(if B1 is 1000, don't subtract C1 from B1 for a sum in C2 (resulting in -1000), until a value is first put in C1. ANOTHER EXAMPLE Don't add the sum of D5 and D6 in D7 if D5 and D6 are blank, preventing D7 (and E7 and G7...) from showing up as 0.00 ). I would like totals to remain blank and not the whole row showing 0.00 until a number is placed to complete the formula
Excelchat Expert 12/02/2018 - 02:32
Yes. I have seen that. I understand that you want to keep a cell blank until, a value has been put in required cells . And, in other cell, if a cell contains a particular value, it should not apply the formula.
Excelchat Expert 12/02/2018 - 02:32
This is doable using IF statements.
Excelchat Expert 12/02/2018 - 02:32
Let's work our way through some examples.
Excelchat Expert 12/02/2018 - 02:33
I have entered few values in B1, C1 and C2
Excelchat Expert 12/02/2018 - 02:34
This is your current scenario 1.
Excelchat Expert 12/02/2018 - 02:34
Am I right?
User 12/02/2018 - 02:35
yes. can i add to it?
Excelchat Expert 12/02/2018 - 02:35
User 12/02/2018 - 02:36
im using the formula =IMSUB(E14,D14)
User 12/02/2018 - 02:36
[number dont apply here obviously]
User 12/02/2018 - 02:37
it does it dont the whoel row though
Excelchat Expert 12/02/2018 - 02:37
No.
User 12/02/2018 - 02:38
so for d2 it then tries e1-d1 which is 0-1500=-1500
Excelchat Expert 12/02/2018 - 02:38
Ok.
Excelchat Expert 12/02/2018 - 02:39
So you want to check if E1 is blank, then formula should not apply. And if E1 has some number only then formula should apply.
User 12/02/2018 - 02:39
and the for e2 it continues f1 (blank which it sees as 0)-e2 which is -1500= -1500 down the whole row
User 12/02/2018 - 02:39
yes
User 12/02/2018 - 02:40
if e1 is blank d1 also should be
Excelchat Expert 12/02/2018 - 02:40
Ok. Let's put some data and find the right formula.
Excelchat Expert 12/02/2018 - 02:40
Where you want to get E1-D1?
Excelchat Expert 12/02/2018 - 02:40
in D2?
User 12/02/2018 - 02:40
oh sorry *d2
User 12/02/2018 - 02:40
yes
Excelchat Expert 12/02/2018 - 02:41
Ok. Easy one. Please see the D2 formula.
Excelchat Expert 12/02/2018 - 02:42
I have written a formula in D2, which makes sure that if there is a number in E1, only then D2 is calculated. If E1 is blank or has other text, then D2 is blank.
Excelchat Expert 12/02/2018 - 02:42
Excelchat Expert 12/02/2018 - 02:42
User 12/02/2018 - 02:43
is there a way to apply that all the way down the row quickly, or do i manually have to change the cell numbers each row?
User 12/02/2018 - 02:43
and yes that works perfectly thankyou
Excelchat Expert 12/02/2018 - 02:43
You can simply drag down the formula till the cell you want.
User 12/02/2018 - 02:46
so for the one in 8-10
User 12/02/2018 - 02:47
would it be for E10 =IF(ISNUMBER(E8), E8+E9, "")
Excelchat Expert 12/02/2018 - 02:47
Yes.
Excelchat Expert 12/02/2018 - 02:48
But that is a different formula than what I have written before.
Excelchat Expert 12/02/2018 - 02:48
May I know what you want to get done? I may help you better then.
User 12/02/2018 - 02:49
add the totals of mastercard and visa in row 10
Excelchat Expert 12/02/2018 - 02:49
Yes, then you are right.
Excelchat Expert 12/02/2018 - 02:49
If E8 is a number, only then it will add E8+E9
Excelchat Expert 12/02/2018 - 02:51
User 12/02/2018 - 02:51
awesome
User 12/02/2018 - 02:51
Excelchat Expert 12/02/2018 - 02:52
Excelchat Expert 12/02/2018 - 02:52
Hope to see you here again soon.
Excelchat Expert 12/02/2018 - 02:52
If you like, I can explain the solution to you.
User 12/02/2018 - 02:52
i search google for an hour and a hour and only can up with a birthday senerio that i couldnt implete into this case
User 12/02/2018 - 02:52
sure
Excelchat Expert 12/02/2018 - 02:52
Ah, I understand, some times Google is frustrating.
Excelchat Expert 12/02/2018 - 02:53
So, in your case, it was a case of checking a condition.
Excelchat Expert 12/02/2018 - 02:53
Excel supports condition checking by an IF function.
Excelchat Expert 12/02/2018 - 02:54
IF has three parts, First is the condition which needs to be checked, Second - Result if condition results true, Third (optional) - Result if condition results in false.
Excelchat Expert 12/02/2018 - 02:55
Here I used ISNUMBER(E1) to check if the cell has number in it. That became the condition which I wanted to check for. Then I told Excel, what to be used when condition results in TRUE or FALSE.
Excelchat Expert 12/02/2018 - 02:55
I hope that answers your question. If you are satisfied, you can close this session.
Excelchat Expert 12/02/2018 - 02:56
Have a nice day ahead. Bye!
User 12/02/2018 - 02:56
User 12/02/2018 - 02:56
i wrote many veriations of =SUMIF(E8,"<>",[E8:E9]) but none worked
Excelchat Expert 12/02/2018 - 02:56
Ok. But that's not the right way to do in your case.
User 12/02/2018 - 02:57
your formula with isnumber make much more sense
User 12/02/2018 - 02:57
thank you so much for your time
Excelchat Expert 12/02/2018 - 02:57
I understand. Sometimes articles doesn't mention clearly in what scenario they can be used. I hope I was able to explain it clearly so you can use it with more ease in future.
User 12/02/2018 - 02:57
have a great night/ day if you are on the other side of the world haha
User 12/02/2018 - 02:58
you did
User 12/02/2018 - 02:58
thanks again
Excelchat Expert 12/02/2018 - 02:58
ha ha :). The whole world is a family.
User 12/02/2018 - 02:58
bye
Excelchat Expert 12/02/2018 - 02:58
Bye!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.