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

Options

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

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!