How to sum non-adjacent cells in a column that equals a given amount.

Ron_C

New Member
Joined
May 21, 2014
Messages
4
I am working on a sheet that has a total amount in cell A2.

I have a column of prices in F3:F.

What I need is a formula that will calculate a subtotal up to the value of A2 without going over.

So far I have:
G3 = F3
G4 = if((F3+F4)<$A$2, sum(F3,F4), "")

Now this works when I autofill down, but it does not continue to sum the non-blank cells.
How do I auto sum non-blank, non-adjacent cells to give a running total that excludes the sum of cells that are greater A2.
The goal is to get a sum of prices that is <= A2, without going over.

TIA
Ron
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Here's some examples of values:

A2 = 100

F3 =19 G3 =19
F4 =15 G4 =36
F5 =73 G5 = blank
F6 =14 G6 =14
F7 =7 G7 =21
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here's some examples of values:
Are they examples of values you have or values you have in F and want in G?

If they are the values that you want in G, how did you get 36 for G4?

A better way to give sample data and expected results: XL2BB
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top