SOLVED

Highlighted
New Contributor

# 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).  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
Highlighted
Best Response confirmed by Templar70 (New Contributor)
Solution

# Re: Sum of Range based on numbers in a different row

``=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)``
Highlighted

# Re: Sum of Range based on numbers in a different row

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

Highlighted

# Re: Sum of Range based on numbers in a different row

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

Regards

Highlighted

# Re: Sum of Range based on numbers in a different row

Both formulas deliver the same result in Microsoft 365.

Highlighted

# Re: Sum of Range based on numbers in a different row

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

Highlighted

# Re: Sum of Range based on numbers in a different row

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

Highlighted

# Re: Sum of Range based on numbers in a different row

That looks better!

Cheers