Forum Discussion
Excel Formula/function
Hello, I'm trying to calculate the difference in a column of cells from the number 40 when the number is greater than 40 and then get the total of the differences. I can't seem to group or nest the...
Suppose your numbers are in A2:A200:
=SUMIF(A2:A200,">40",A2:A200)-40*COUNTIF(A2:A200,">40")
=SUMIF(A2:A200,">40",A2:A200)-40*COUNTIF(A2:A200,">40")
Here are two other options:
=SUMPRODUCT((A1:A10>40)*(A1:A10-40))
or
=SUM(IF(A1:A10>40,A1:A10-40,0))
The latter is an array formula and must be confirmed with Ctrl-Shift-Enter.
