Jun 10 2020 10:43 AM
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.
Jun 10 2020 11:58 AM
Solution=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)
Jun 10 2020 12:04 PM - edited Jun 10 2020 12:19 PM
This is exactly what I needed. I'm using Office 365, so the first formula works perfectly. Thank you so much!!
Jun 10 2020 12:11 PM
I think you need to check your second formula with SUMPRODUCT, which does not give correct results in any version of Excel.
Regards
Jun 10 2020 12:33 PM
Jun 10 2020 01:15 PM - edited Jun 10 2020 01:18 PM
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
Jun 10 2020 01:42 PM
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)
Jun 10 2020 01:47 PM
Jun 10 2020 11:58 AM
Solution=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)