# Sum cells only up to a specific value in each cell

#### rrmando

##### Board Regular
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.

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

Awesome...works like a charm...thank you!!!
You're welcome. Thanks for the feedback!

Replies
2
Views
328
Replies
3
Views
349
Replies
0
Views
301
Replies
7
Views
288
Replies
4
Views
332

1,203,028
Messages
6,053,123
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back