SOLVED

Sum of Range based on numbers in a different row

Copper Contributor

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).  This is for an employee who is working a flexible schedule and is not working the same amount of hours each day.  The issue is if the employee is off for the full day there is an X in the field.  That X is represented by a value in the row directly above it; each day in the month is given a value.  If the employee is off for only a partial work day, the number of hours is entered into the field instead of the X.  I have attached a sample of the spreadsheet, which should help better explain what I am trying to accomplish.  Thank you.

7 Replies
best response confirmed by Templar70 (Copper Contributor)
Solution

@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 

This is exactly what I needed.  I'm using Office 365, so the first formula works perfectly.  Thank you so much!!

@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

@Jos_Woolley 

Both formulas deliver the same result in Microsoft 365.

 

@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

@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)

 

@Detlef Lewin 

 

That looks better!

 

Cheers

1 best response

Accepted Solutions
best response confirmed by Templar70 (Copper Contributor)
Solution

@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)

View solution in original post