Forum Discussion

gina davis's avatar
gina davis
Copper Contributor
Aug 16, 2017

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

Formulas & Functions
tips and tricks

2 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 16, 2017
    Suppose your numbers are in A2:A200:
    =SUMIF(A2:A200,">40",A2:A200)-40*COUNTIF(A2:A200,">40")
    • IngeborgHawighorst's avatar
      IngeborgHawighorst
      MVP
      Aug 16, 2017

      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.