Is there an Excel formula to limit each number in a SUM formula to a certain value?

I've found how to create a cut-off for the total, but not each item.

An example of what I mean: I'm summing 6 numbers (in each row), but if a number is larger than 120 I want it to only be counted as 120 when being summed. (So I don't have to look over the 6#s to see if the total was inflated by 1 of the #s being VERY large, I'll know it's "balanced").

I figure I could put a MIN formula over each number, inside the sum (SUM(MIN(120,cell1),MIN(120,cell2)... etc), but if there's something simpler out there, I'd like to know (if only for future reference where I maybe dealing with more numbers per-sum).

2 Answers

Relevance
  • 4 years ago
    Favorite Answer

    I would create a few hidden columns that have IF statements checking the values of your fields. if it's more than 120, then result 120, otherwise the cell being checked:

    B1 = IF(A1>120,120,A1)

    then when you have your sums, sum up the hidden column.

    then you can =SUM(B1..B100) (via a range, vs. adding up each individual cell), and if you need more, just make the range longer. And it's easy to copy the formulas from one cell to another.

  • ?
    Lv 7
    4 years ago

    SUMIF

Still have questions? Get your answers by asking now.