0

Lets say my data has a column of numbers in A. I want the sum of A unless the number in A is greater than some threshold. Conceptually I want SUM(MIN(A:A, 1000)), but this will return just the single smallest number.

In R there is pmin, which is a parallel minima, so I could write SUM(PMIN(A:A, 1000)) and each value in A is compared against 1000.

In Excel, I could create a new column B that was MIN(A1,1000) and drag it down. But I don't want to create a new column. I want to compute the sum in a single equation. Any solution?

Improve this question
3
  • You can use sumif() for this: =SUMIF(A:A, "<1000",A:A ) Commented Sep 20, 2018 at 17:22
  • 2
    =SUMIF(A:A, "<=1000") + 1000 * COUNTIF(A:A, ">1000") Commented Sep 20, 2018 at 17:22
  • Make that an answer, @brittenb. Thanks. Commented Sep 21, 2018 at 2:22

1 Answer 1

Reset to default
1

If you don't want to make a new column, you'll need to do it in two parts. The first part sums all of the values that are less than or equal to 1,000, while the second part adds 1,000 for each value that is over that value. It should be obvious that you would just replace 1,000 with whatever threshold you'd like to impose. Here is the code:

SUMIF(A:A, "<=1000") + 1000 * COUNTIF(A:A ">1000")

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.