Sum cells only up to a specific value in each cell

[rrmando]

Hello all. I need to add the range below for all cells (sorry can't get it to show each number under each day), but only up to 8 of each cell. So the total for the range below would be 8+8+8+6+8=38.

For example, Wed =IF(C2>8,8,C2), so 8 is returned for Wednesday. Thur =IF(D2>8,8,D2) returning 6. Aside from using multiple IFs statements, is there a more efficient formula to use? Thank you.

Monday Tuesday Wednesday Thursday Friday
8 8 10 6 11

 

[T. Valko]

Hello all. I need to add the range below for all cells (sorry can't get it to show each number under each day), but only up to 8 of each cell. So the total for the range below would be 8+8+8+6+8=38.

For example, Wed =IF(C2>8,8,C2), so 8 is returned for Wednesday. Thur =IF(D2>8,8,D2) returning 6. Aside from using multiple IFs statements, is there a more efficient formula to use? Thank you.

Monday Tuesday Wednesday Thursday Friday
8 8 10 6 11

Try this...

Assuming the numbers are in the range A2:E2.

Array entered**:

=SUM(IF(A2:E2>=8,8,A2:E2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[rrmando]

Awesome...works like a charm...thank you!!!

 

[T. Valko]

Awesome...works like a charm...thank you!!!

You're welcome. Thanks for the feedback!