Defining Calculations
I am building a metrics sheet for project costings, that references another sheet (time report).
Below are the columns so far and I am looking to add a few more to further break down the data. In the Cost column is the formula =([Days On Site]$2) * [Total Hours]@row, which is referring to the Total Hours multiplied by a specific cost figure from a small table at the top of the sheet.
The below formula calculates the number of hours spent Commissioning for a specific project:
=IFERROR(SUMIFS({Hours}, {Area}, OR(@cell = "Commissioning"), {Project}, OR(@cell = [Primary Column]@row)), 0)
Is it possible to add to this formula to differentiate between Weekday or Weekend hours, which are different rates, and multiply accordingly, i.e. some hours spent Commissioning MonFri, but they also worked SatSun?
Answers

=IFERROR(SUMIFS({Hours}, {Area}, OR(@cell = "Commissioning"), {Project}, OR(@cell = [Primary Column]@row), {Weekday}, IF(WEEKDAY@cell) >= 5), 0))
or
=IFERROR(SUMIFS({Hours}, {Area}, OR(@cell = "Commissioning"), {Project}, OR(@cell = [Primary Column]@row), {Weekday}, NOT(WEEKDAY@cell) = 6, 7), 0))
I'm trying to work out how to get the formula to calculate the hours worked MonFri but it's returning #UNPARSEABLE, which means I haven't quite got it right. If I can get this one working, then I would need another to only include Saturday/Sunday and ignore the other weekdays.
Then I would want to add to this formula the below to include the multiplication to a value in a fixed cell:
=([Days On Site]$2) * [Total Hours]@row

Sorry, I meant to have one formula that if the hours are MonFri, it multiplies by the figure in the first column, and if the hours fall on SatSun, then it multiplies by the figure in the second column. Is this possible or would I need to have 2 columns, i.e. commissioning MonFri, then another for SatSun?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!