Excel Formula/function

[gina davis]

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 right formula to achieve this.  Any help?

[#2]

Suppose your numbers are in A2:A200:
=SUMIF(A2:A200,">40",A2:A200)-40*COUNTIF(A2:A200,">40")

[#3]

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.