We help IT Professionals succeed at work.

excel formula sum if issue

Wilder1626
Wilder1626 asked
on
Medium Priority
271 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Do you mean you want a number in I12:I18? What should appear in your example?
CERTIFIED EXPERT

Author

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?

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

=IF(G12="","",$I$10-SUMIF($G$12:G12,1,$E$12:E12))
StephenNetwork Infrastructure Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
CERTIFIED EXPERT

Author

Commented:
ok, let me take a look at all this, and i will let you know.

Thanks again for the fast response
CERTIFIED EXPERT

Author

Commented:
Oh thank you so much.

I will take these 2 answers.

Thanks again
Thanks for nothing.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.