How would you combine the CONTAINS or HAS function with a SUMIFS formula?

I am trying to create a formula which sums the total number of machines we have delivered for certain projects, but in the "Project Name" column, we have different stages of the same project which I want to combine. E.g.

Project Pearl 1.1 - 5 machines delivered

Project Pearl 1.2 - 5 machines delivered

Project 1.3 - 5 machines delivered

So my CONTAINS/HAS formula would search for "Pearl" and return 15, but I keep getting INVALID OPERATION errors when I try the following:

=SUMIFS({Projects Sheet Range 1}, {Project Sheet Range 3}, "Hardware Delivery", HAS{Project Sheet Range 9}, “Pearl”)

Range 1 - Quantity field

Range 3 - Activity field

Range 9 - Project Name field

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You will need to use "@cell" for the range within the HAS function, and there is an open parenthesis missing from immediately after the HAS function.

I also see some slanted quotes there around Pearl. Notice how the others are straight up an down? Those slanted ones are called "smart quotes" which (ironically enough) Smartsheet does not recognize as valid characters in formulas. Make sure formulas are typed in Smartsheet directly, here in the Community, or in a text editor such as Notepad (not Word).

=SUMIFS({Projects Sheet Range 1}, {Project Sheet Range 3}, "Hardware Delivery", HAS(@cell, "Pearl")

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.