How do I summarize data by a particular condition and by Date/Year?

Hello,

I am attempting to create a dashboard that will summarize the cost of overtime for employees working at various locations on a month by month basis to compare cost between either/both conditions (by location and/or by month). In order to do this I have parsed out that I will have to summarize the data in a separate sheet before building my dashboard because the sheet that is being pulled from is not organized (8000 row by 50 column sheet where all employees enter timesheet). I have managed to pull cost of OT by location (as below "a)"), but would like to also add a month, year criterion. I have attempted to pull just the Year/Month using the DATE function (as below "b)"), but results are coming back as $0 (should be approximately $600).

a) =SUMIFS({OT Cost}, {Location}, "Site")

b) =SUMIFS({OT Cost}, {Location}, "Yard", {Date}, DATE(2022, 5))

Any insight would be greatly appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this for May of 2022.

=SUMIFS({OT Cost}, {Location}, "Site", {Date}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2022))

All comments

Paul Newcome

Try something like this for May of 2022.

=SUMIFS({OT Cost}, {Location}, "Site", {Date}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2022))

Anna WJ

Paul Newcome, that worked like a charm!

I was trying all sorts of different referencing of the date column, but everything was coming back "Unparseable" and the equation I noted in my question was the only one coming back with (incorrect data).

Thank you for your help!

Paul Newcome

Happy to help. 👍️

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.