**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

Where do need the answer?

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

Give me a moment please

Excelchat Expert
08/02/2018 - 07:32

I am already working

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

Session is about to end

**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.*