excel formula sum if issue

Hello all,

I need you help on a formula.

If you look at my excel sheet, in Range("I10"), i have the total money available.

In column E, i have the Gift card value and In column G, this is where i will put Yes, every time that i give a card.

Finally, in Column I is the Money available left after giving a card.

I need to deduct from the Money available Range("I10") every time that i put yes in column G, the value from the same lane in column E.

How can i do this please?

Thanks for your help.


Money-left.xlsx
LVL 11
Wilder1626Asked:
Who is Participating?
 
dlmilleCommented:
1.  You can put this in Range("I10"):

=500-SUMPRODUCT(E12:E18,G12:G18)

2.  You can also put the results into a rolling balance in column I.  I noticed you're using 1 instead of Yes, so my formulas work with that.

See attached for the second example:

Dave

Money-left.xlsx
0
 
StephenJRCommented:
Do you mean you want a number in I12:I18? What should appear in your example?
0
 
Wilder1626Author Commented:
So in I12, i would have Range("I10") - E12 = 450.00

If i add another yes in G13, then in I13, i will have the Money available of 500 less the first value from E12 - the second yes with value from E13. So the result in I13 would be 390.00.

Does it make sense?

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
StephenJRCommented:
Not sure where 390 comes from, but what about this?

=IF(G12="","",$I$10-SUMIF($G$12:G12,1,$E$12:E12))
0
 
sjl1986Commented:
Here's the work sheet. I moved your cells around a little to make it easier to understand and formulate.

Basically, the code you want is =IF(G12="yes",I11-E12,"")

This looks at G12 to see if you typed "yes" in there, if you typed yes, then it will subtract E12 from I11 (which is $500-50) and you get $450. If you put ANY other text besides "yes" then the cell would just stay blank because the text does not meet the IF requirements. That is what the "" means after the last comma.

Check out the sheet and see how it works for you. Every time you put yes in the next row, it subtracts your amount from the total "money left" column.

You can change the formula to take the whole gift column out if you want to make it easier. I added a second sheet to show.

In the simplified version. You simply add how much the gift card was, and the money left column reflects that card. So you only add money when you give the card away, you don't have to say "yes" every time. Whichever works better for you is fine though.


Copy-of-Money-left.xlsx
Copy-of-Money-left--Simplified-.xlsx
0
 
StephenJRCommented:
Although I don't really understand why you don't just have a single cell which tells you how much money you have left.
Also you refer to 'yes', but column G has 1s in it.
0
 
dlmilleCommented:
If, rather, you did want to use "Yes" instead of 1:

1.  1.  You can put this in Range("I10"):

=500-SUMPRODUCT((E12:E18)*(G12:G18)="Yes")

2.  see attached using "Yes" for forumlas as rolling balances:




Money-left-r2.xlsx
0
 
Wilder1626Author Commented:
ok, let me take a look at all this, and i will let you know.

Thanks again for the fast response
0
 
Wilder1626Author Commented:
Oh thank you so much.

I will take these 2 answers.

Thanks again
0
 
StephenJRCommented:
Thanks for nothing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.