Sum multiple Columns, but not exceeding a certain value.

[Dashminus]

Good morning.

I am not real sure how to describe this question in a concise way, so I will just go ahead and give it a shot.

Scenario:

I have selected a number. For this scenario, that number is 30.
I have two columns, (Minus) and (Plus). One column will subtract from the Max Value. The other column will add to the Max Value.
I have a cell (Current Total) that will actively display the current total as values are inputted into either column.
As values are inputted into the "Minus" and "Plus" columns, they are either added or subtracted from 30, and the results are displayed in the "Current Total" cell.

Special circumstances:
1. The "Current Total" should never go above the max value I selected (30), even if the "Plus" column totals more than the "Minus" column.
I used =MIN(30,(E3-SUM(C11:C19)+SUM(D11:D19))) to achieve this.

The problem:

If the "Add" column totals more than 30, the "Current Total" correctly displays 30, but anything above 30 should not impact the subtracted column.
I am trying to get a running total of the two columns, with the total never going above the Max Value.

Picture Example:

1. Max number is 30.
2. 2 is inputted in the "Minus" column. 28 is correctly displayed.
3. 1 is inputted into the "Minus" column. 27 is correctly displayed.
4. 10 is inputted into the "Plus" column. 30 is correctly displayed, but the leftover 7 is still in the calculation, just not seen (37).
5. 6 is inputted into the "Minus" column. What is desired is that 6 is subtracted from the "Current Total", instead of it being subtracted from that true total (37) that is unseen.
6. The expected value for the "Current Total" is 24

Attachment 643983


Does this make sense?

Thank you in advance for any help.

[Pete_UK]

Duplicate thread.

[alansidman]

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: link to be added here