need help adding year to a SUMIF formula.

I have a cross sheet formula that sums activities based on "area" and displays in a dashboard.

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="West")

This formula is currently working. I need this same calculation to perform based on start date year. Currently, all the start dates are after 01/01/2022. Once we begin adding 2023, these are the only dates I want in the calculations to be used for a dashboard.

Here is what I have tried:

=SUMIFS(Start Date:Start Date, Year:Year, 2023, {ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central") - receive UNPARSABLE

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date} this formula returns as #INVALID REF

Thanks,

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You would use something like this:

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date}, IFERROR(YEAR(@cell), 0) = 2023)

kscott01

thanks for your assistance. However, I am receiving this error:

Paul Newcome

I don't see an error in your screenshot.

kscott01

Paul Newcome

You will need to follow the appropriate steps to create the cross sheet reference (the same way the other cross sheet references were made)

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.