Limit sum to 100

[ExcelGeek90]

Can anyone please help?

Working in a column 1 where applying SUMIFS formula from another sheet having 51 different values. Among those 51 values I just need to see in Column1 upto sum 100 values. 

For examples  1st,2nd and 3rd value which are 50,25,25 respectively make sum of 100  which is fine 

but in some case 1st,2nd and 3rd value which are 50,35,35 respectively make sum of 110 so in this case how I can restrict sum to limit upto 100 only ?

Looking for support 

Thanks 🙂 

[#2 · Accepted solution]

@ExcelGeek90 

You can simply use:

=MIN(100,SUM(A1:A50)

In case the sum exceeds 100, it will display only 100.

[#3]

Thanks @Angosto.