Forum Discussion

Templar70's avatar
Templar70
Copper Contributor
Jun 10, 2020
Solved

Sum of Range based on numbers in a different row

Hi, I would appreciate any help I can receive on this spreadsheet, which is being used for tracking employee absence.  I am trying to sum the total of a range.(the sum of the hours taken in the month...
excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jun 10, 2020

    Templar70 

    =SUM((B5:AF5="X")*(B$4:AF$4),B5:AF5)

    or for older versions of Excel:

    =SUMPRODUCT((B5:AF5="X")*(B$4:AF$4),B5:AF5)
Jos_Woolley's avatar
Jos_Woolley
Iron Contributor
Jun 10, 2020

Detlef_Lewin 

 

I think you need to check your second formula with SUMPRODUCT, which does not give correct results in any version of Excel.

 

Regards

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jun 10, 2020

Jos_Woolley 

Both formulas deliver the same result in Microsoft 365.

 

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    Jun 10, 2020

    Detlef_Lewin 

     

    Then you must have amended the original values as given by the OP, since it will only be by a very fortunate coincidence that those two formulas produce the same result: SUM sums the arguments passed to it, SUMPRODUCT sums the products of the respective entries in the arguments passed to it.

    Regards

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Jun 10, 2020

      Jos_Woolley 

      Sorry, you are correct.

      I thought I posted the correct formula.

      This is the correct one:

      =SUM(SUMPRODUCT((B5:AF5="X")*(B$4:AF$4)),B5:AF5)

       

Resources