Using SUMIFS with Cross Sheet References and CONTAINS

There seems to be lots of information on how to use SUMIFS with a CONTAINS criteria, referring to another sheet.

Here is my formula on Sheet 1:

=SUMIFS({Total Cost - Final Event Space Cost}, {Requested Event Date}, {Requested Event Date} > TODAY(), {Requested Event Date}, {Requested Event Date} >= TODAY(-365))

Total Cost - Final Event Space Cost is the range of cells to be summed on Sheet 2
Requested Event Date is a date field on Sheet 2 that will be evaluated against criterion
The next section is evaluating the Requested Event Date to determine if a date is in the last 365 days.

I would like to add criterion, evaluating a status field on Sheet 2 called "Event Status", where the status contains the word "Scheduled".

How would I add a CONTAINS or HAS condition in the SUMIFS?

Find more posts tagged with

Formulas

field calculation

SumIfs

Accepted answers

All comments

Mike Chiu

Try this:
=SUMIFS({Total Cost - Final Event Space Cost}, {Requested Event Date}, {Requested Event Date} > TODAY(), {Requested Event Date}, {Requested Event Date} >= TODAY(-365), {Sheet 2 Status}, CONTAINS("Scheduled", [Event Status]:[Event Status]))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.