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
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!