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 Mon-Fri, but they also worked Sat-Sun?

Find more posts tagged with

Accepted answers

All comments

Michelle Maas

=IFERROR(SUMIFS({Hours}, {Area}, OR(@cell = "Commissioning"), {Project}, OR(@cell = [Primary Column]@row), {Weekday}, IF(WEEKDAY@cell) >= 5), 0))

=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 Mon-Fri 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

Michelle Maas

Sorry, I meant to have one formula that if the hours are Mon-Fri, it multiplies by the figure in the first column, and if the hours fall on Sat-Sun, then it multiplies by the figure in the second column. Is this possible or would I need to have 2 columns, i.e. commissioning Mon-Fri, then another for Sat-Sun?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.