Forum Discussion

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...

sample flex leave tracker.xlsx48 KB

excel

Formulas and Functions

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)

Detlef_Lewin

Silver Contributor

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

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
Silver Contributor
Jun 10, 2020
Jos_Woolley
Both formulas deliver the same result in Microsoft 365.
- 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
    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)