Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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
0
Wilder1626
Asked:
Wilder1626
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
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
 
StephenJRCommented:
Not sure where 390 comes from, but what about this?

=IF(G12="","",$I$10-SUMIF($G$12:G12,1,$E$12:E12))
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now