In google sheets how do I sum the positive difference to 10 in a range of numbers?-CodePudding

I have an array of numbers and for every number I want to check if it's greater than a value in another cell and if it is greater I want to add the difference to the total sum.

I have succeeded to do this "manually" for an amount of cells but there must be a better way. For simplicity I just compared the value to 10 but it will be another cell.

=sum(if(A1>=10,A1-10,0),if(A2>=10,A2-10,0),if(A3>=10,A3-10,0))

The formula abohe yields the expected result for A1:A3.

What unfortunately doesn't work is:

=SUM(if(A1:A3>=10,A1:A3-10,0))

CodePudding user response：

At the end I changed my approach to arrive at the solution:

=SUMIF(A1:A3,">10") - COUNTIF(A1:A3,">10") * 10

So instead of summing the differences directly, we sum the appropriate values and then subtract the reference as often as we summed up.

CodePudding user response：

Try with this:

=SUM(ARRAYFORMULA(IF(A:A="","",IF(A:A<=10,10-A:A,0))))

CodePudding user response：

try:

=BYROW(A1:A20, LAMBDA(a, IF(a>=10, a-10, 0)))

or:

=SUMPRODUCT(BYROW(A1:A20, LAMBDA(a, IF(a>=10, a-10, 0))))