# Excel - IF Function Problem - Expert Solution

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

I need help with subtraction following true/false criteria. For example a1 has a value. c1 has a value, d1 has a y for yes or is left blank for no.e1 has value, f1 y or blank and so on. I need to subtract the values to the left of the column with "y" from a1 and each each other y column to get an answer into let's say k1. a1=30,000 a3=2500. a4=y. a5=1500, a6="blank". a8=2000. a9=y. ai-a3-a5=a8 if y to the right.
Solved by G. A. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/02/2018 - 07:21
Welcome to Got it Pro Excel!
User 08/02/2018 - 07:21
hello
Excelchat Expert 08/02/2018 - 07:22
Would you mind if you made a quick example here of the answer you are expecting?
User 08/02/2018 - 07:24
Yes
Excelchat Expert 08/02/2018 - 07:24
Thank you!
User 08/02/2018 - 07:26
The 1st value is lets say an account balance. The y is shown if paid blank if unpaid. Subtracting paid from balance to get new balance
Excelchat Expert 08/02/2018 - 07:27
OK
Excelchat Expert 08/02/2018 - 07:28
the firts 30k is an unpaid balance
Excelchat Expert 08/02/2018 - 07:28
in A1
User 08/02/2018 - 07:28
correct
User 08/02/2018 - 07:28
correct
Excelchat Expert 08/02/2018 - 07:28
C1 is a balance Paid
User 08/02/2018 - 07:28
yes
User 08/02/2018 - 07:29
e1 is unpaid i1 is paid
Excelchat Expert 08/02/2018 - 07:29
So you need to get all the ones with "y" to the right
Excelchat Expert 08/02/2018 - 07:29
?
User 08/02/2018 - 07:29
yes each one with a y subtracted fro a1
Excelchat Expert 08/02/2018 - 07:30
Ok
Excelchat Expert 08/02/2018 - 07:30
Excelchat Expert 08/02/2018 - 07:31
You need the data with this structure?
User 08/02/2018 - 07:31
ian the a row just somewhere to the right
User 08/02/2018 - 07:31
Like L1
Excelchat Expert 08/02/2018 - 07:31
OK
Excelchat Expert 08/02/2018 - 07:32
Excelchat Expert 08/02/2018 - 07:32
Excelchat Expert 08/02/2018 - 07:35
=A1-ArrayFormula(sum(if(C1:J1="y",B1:I1)))
Excelchat Expert 08/02/2018 - 07:35
Are you using Excel Desktop?
User 08/02/2018 - 07:36
Actually it is in MS Works database but I believe it will accept an array formula.
Excelchat Expert 08/02/2018 - 07:37
Great!
Excelchat Expert 08/02/2018 - 07:37
Can you try with this formula?
User 08/02/2018 - 07:37
give me a moment
User 08/02/2018 - 07:41
Do i need to do control shift enter?
Excelchat Expert 08/02/2018 - 07:41
Yes
User 08/02/2018 - 07:41
Remove the words array formula correct?
Excelchat Expert 08/02/2018 - 07:41
Sorry I forgot to mention it
Excelchat Expert 08/02/2018 - 07:41
Yes
Excelchat Expert 08/02/2018 - 07:42
A1-sum(if(C1:J1="y",B1:I1))
Excelchat Expert 08/02/2018 - 07:42
with CTRL+SHIFT+ENTER
Excelchat Expert 08/02/2018 - 07:46
Any luck?
User 08/02/2018 - 07:46
I am gettig a 0 result
Excelchat Expert 08/02/2018 - 07:47
The result here is correct right?
User 08/02/2018 - 07:47
yes
Excelchat Expert 08/02/2018 - 07:47
Can you copy and paste the formula you are using in MS Database
Excelchat Expert 08/02/2018 - 07:47
?
Excelchat Expert 08/02/2018 - 07:48
MS Works*
Excelchat Expert 08/02/2018 - 07:51
=A1-sumproduct((C1:J1="y"),(B1:I1))
Excelchat Expert 08/02/2018 - 07:51
Try this one without the array formula
Excelchat Expert 08/02/2018 - 07:54
Are you still there?
Excelchat Expert 08/02/2018 - 07:59

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.