SUMIF (Up to 100%)

[GinaMoses]

I need to create a formula that sums multiple columns based on criteria but stops at 100% or at 40 (which is 100%). What I have now doesn't sum all of the way up to 40 because it will not split the totals that it's summing.

=IF($E$125<=40,(SUMIF($C$126:$D$136,Q126,$D$126:$D$136)+SUMIF($E$126:$F$136,Q126,$F$126:$F$136)),0)

 

[ETAF]

not sure i follow
if cell E125 is less than 40 then the sum works regardless

you could apply a MIN ()
not sure where in the formula that needs to be -

BUT if you want the SUM() to never go above 40 - then

MIN ( (SUMIF($C$126:$D$136,Q126,$D$126:$D$136)+SUMIF($E$126:$F$136,Q126,$F$126:$F$136)) , 40 ) should do that

What version of excel are you using - ALSO

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.

The forum does allow for spreadsheets to be uploaded direct to the forum -

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

 

[GinaMoses]

Yes that works the same. But my problem is that (E125) is a sum of numbers as well and I need that column not to exceed 40 but to sum up to 40. I am using a unique small formula in Q126 as my criteria and I want the (MIN or SUMIF) formula to spread over the array. Right now it's only adding to 38 and I need it to spread up to 40. The sum of coumns D126:D126 & F126:126 is 46. However with the MIN formula above, it stops at Column D because Column F makes the product over 40.

 

[ETAF]

sorry not really following, other members may understand.

as mentioned in my earlier post

What version of excel are you using - ALSO

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.

The forum does allow for spreadsheets to be uploaded direct to the forum -

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Reply