Subtracting from the addition of 2 specific cells with a given condition / given conditions.

[kairoselvarro]

Hello.

I was messing around in Excel and I want to subtract from the addition of one specific cell to another, only if the product is larger than a given criteria.
So e.g. B1=1500 , C8 = 5692 , condition = ">6400"

So if possible, I would want to write something like =SUMIF((B1+C8),">6400",-6400) or something like that.

I have 3 main problems:

1. The SUMIF function apparently only works for ranges of data, not specific cells. In this case, I only need 2 specific cells, i.e. B1 and C8. Am I using the wrong function then?

2. I don't know how to include the -6400 part if the conditions are met. How do I do that?

3. In the future, if let's say I would like to add more conditions, how do I introduce more conditions? Is it even possible in the first place?

If someone can kindly advise me, I would appreciate it very much, thank you...

[#2]

How would you use a yes or no value? For example: I am building a accounts receivable sheet that would in include a formula like if A10 = yes the subtract A9 from M10 (which would be the total balance of all accounts owed.

[#3]

@kairoselvarro 

Unfortunately, I can't follow your thoughts, it's probably because of the translator.
Please insert a file where you explain exactly what you want to accomplish, both will be helped.

Nevertheless, here is an approximate example, as I understand it).

In A1 is the Formula. 

Hope I could help you with this ... otherwise please file :))

Nikolino

I know I don't know anything (Socrates)

[#4]

@NikolinoDEI have attached an example of what I am working on below.  If I7 for example says says N I would like the amount in H7 to forward to K7. I'm thinking an IF then formula.  I hope that explains it If not please let me know.

Thanks for helping.

[#5 · Accepted solution]

@NikolinoDE Okay, my bad...I'm literally a newbie at this...

Let me just start by saying for all who are going to read this thread that I've solved my issue! I was messing around in Excel on my own and finally figured it out! But just to make it clear, I will outline the problem that I had up there ^ according to your "way" (for lack of better word...) and I'll also post how I solved it!

Problem:

A1 = 5

A2 = 12

B1 is the Formula (the one I had a problem with)

I wanted to code the B1 cell with a formula that would allow me to set my own arbitrary conditions, and change its value accordingly. In this example, Condition = If (A1+A2) > 10, (A1+A2)-10.

I don't know if that makes sense, but that's how clear-cut I can be...

Solution:

I fiddled around with the functions, and I realised that what I required wasn't a Mathematical function: it was a Logical one!

so, all I had to do was use the IF function (it's pretty dumb of me tbh, now that I think about how I was sweating beads trying to figure that bit out...). It becomes like this:

B1 =IF( (A1+A2)>10 , (A1+A2)-10 , (A1+A2) ) [LITERALLY type this expression into the cell]

The IF Syntax is: =IF( logical_expression , value_if_true , value_if_false )

So...for any beginners out there wanting to us the IF function like me about 2 weeks ago...this roughly translates to:

Condition: (A1+A2) is more than 10.

Value if true: if (A1+A2) is more than 10, subtract 10 from (A1+A2).

Value if false: if (A1+A2) is not more than 10, return (A1+A2) i.e. don't do anything to (A1+A2).

Using the data up there, A1+A2=17, so the B1 cell would return 7.

I really hope this helps anyone out there!! I'm not a tech whiz myself, and I don't even know whether I have the right to give these tips in the first place but...if I was a newbie in Microsoft and asking the forum regarding something difficult, I would much rather have the help than having none at all. Thank you for being so ready to help me out @NikolinoDE!! I really appreciate it :))

[#6]

Okay, I don't fully grasp the picture you're trying to paint here, but I'm gonna try my best. So according to what I can piece together from your post, you meant to say:
M10 = total balance of all accounts owed
A9 = value
A10 = "Yes"
...yes?

Assuming that yes, this is what you meant, then it's very simple, really.

All you have to use is the IF function. So, you will need a new cell that's NOT M10, A9, or A10, because this would be a new expression, and required a brand new blank cell. For example, you'll be using B10:
B10 = IF(A10="Yes",(M10-A9),A9)
Condition: A10 contains "Yes"
Value_if_true: If A10 contains "Yes", subtract A9 from M10
Value_if_false: If A10 does not contain "Yes" (i.e. any other words that's not "Yes"), then return A9.

Granted, I don't know what's your exact Value_if_false, so you might wanna figure that bit out...

Okay, that's all I can glean from your example. I'm really sorry if this didn't answer your question at all, but...I might need more detail so that I can help you. Feel free to reply here if you still have queries and I'll do my best to help. All the best with Excel friend :)

[#7]

@kairoselvarroIf I was less than concise I apologize. Explaining things like this may not be my best suit.

So if I9 says Y or Yes I would  like the amount in H9 to appear in K9 so it get it added to the running tally at K35. This may not be the best way of doing it and if you can suggest a better way by all means please do.

Thanks you guys. 

[#8]

@Robert_Ross 

I am not quite sure whether this is desired.

But if you want it, please mark it as the best answer so that other members can also benefit from it.

If this is not what you are looking for please ignore it.

Suggested solution in the inserted file.

Nikolino

I know I don't know anything (Socrates)

[#9]

You sir are a genius. That's exactly what I was looking for. Thank you. Now do I mark your reply as best answer?

[#10]

Should say How do I mark best answer?

[#11]

I can mark it as a best response! 

However, this isn't exactly the answer I'm looking for for the problem I have up there ^ so I won't mark it as the best response. It sounds like this is another issue that is not reflected in my question. Please start a new question and you can mark it as your best response! I hope this helps...

Thank you so much for contributing in this post, though. I really can't thank you guys enough.

[#12]

No need I have what I need. Thank you again