Sum with a condition

[papichulo2nite]

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

 

[papichulo2nite]

If a native solution takes to much time I will take a VB version until I figure out the native. (Stubborn I am...)

 

[NARAYANK991]

Hi ,


Can you try this and see if it works :


=IF(SUM($A$1:A2)>$F$2+SUM($B$1:B1),SUM($A$1:A1)-SUM($B$1:B1),"")


where your data is in column A , starting from A2 , and your formulae are in column B ; the value of 1348 is to be entered in F2.


Narayan

 

[nazmul_bd08]

col A col B col C col D col E col F(RESULT) COL G (result)

[code]5[code][code][code]3[code][code][code]6[code][code][code]1[code]

[/code][/code]4[/code][/code]=9[/code][/code][/code][/code][/code][/code][/code]`=19

here col F COUNT ALWAYS 1 & 2 OTHERWISE NUMBER COUNT COL G

HOW

PLS SOLVE THIS PROPLEM

 

[Hui]

Nazmul_bd08


When posting please don't add a post to the end of another post

It makes reading and searching rather difficult


Please start a new post here: http://chandoo.org/forums/?new=1


You may also want to consider uploading a sample file, as I don't understand your requirements

 

[papichulo2nite]

Narayan,


That worked thank you! Simplicity is the key, I was trying to make it too difficult!