Excel Sum with condition

Question

Hello All,

I have seen some awesome solutions on here but have not been able to find my scenario.

I need to sum the values in a column until they reach a maximum number less than the given value (1348), report that max value (1325), and then do it again starting where the last one left off(1250) until the end of the column (700).

Here is the example:

PSF Max PSF= 1348

1000 x

325 x Sum x's = 1325

300 xx

275 xx

250 xx

225 xx Sum xx's = 1250

200 xx

175 xxx

150 xxx

125 xxx Sum xxx's = 700

100 xxx

75 xxx

50 xxx

25 xxx

0 xxx

Hope the question is clear. I know this can be done without VB and that is what I need. I have played with MMult and found another post with the following:

=MIN(IF(SUBTOTAL(9,OFFSET($A$1,0,0,1,COLUMN($A$1:$F$1)))>=A2,SUBTOTAL(9,OFFSET($A$1,0,0,1,COLUMN($A$1:$F$1)))))

which worked for summing a row but I could not get it to work out for what I was trying to do

Answer 1

Hi,

Try this

1. Suppose numbers are in range A6:A20
2. In cell A2, enter 1348
3. In cell B5, type Max
4. In cell B6, enter this formula and copy down

=IF(OR(SUM(INDEX(A$1:A6,LOOKUP(2,1/(B$1:B5<>""),ROW(B$1:B5))+1,0):A6)+A7>$A$2,A7=""),SUM(INDEX(A$1:A6,LOOKUP(2,1/(B$1:B5<>""),ROW(B$1:B5))+1,0):A6),"")

Hope this helps.

Answer 2

The following assumes:

• The data are in A2 and down.
• The limit is in C1
• B1 does not contain a number (it may contain text or be empty)

In B2, enter the formula

=IF(A3="",SUM($A$2:A2)-SUM($B$1:B1),IF(AND(SUM($A$2:A2)-SUM($B$1:B1)<=$C$1,SUM($A$2:A3)-SUM($B$1:B1)>$C$1),SUM($A$2:A2)-SUM($B$1:B1),""))

Fill down.