SOLVED

Limit sum to 100

Copper Contributor

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 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Arslannaz 

 

You can simply use:

 

=MIN(100,SUM(A1:A50)

 

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

Thanks @Angosto.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Arslannaz 

 

You can simply use:

 

=MIN(100,SUM(A1:A50)

 

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

View solution in original post