Sheet Summary Formula

Michelle Maas
Michelle Maas ✭✭✭✭
edited 10/17/22 in Formulas and Functions

The above image shows the three highlighted columns I am working with in the Sheet Summary section. If I just calculate the value by subheading, it works well but I need it to identify the status:

=SUMIF(SubHeading:SubHeading, (CONTAINS("Small Works", @cell)), Value:Value)

What I want my formula to do is to sum total the value column if the type of project in the SubHeading column is "service contract", and the Status is either "Projected", "Schedule" or "Not Stated". I am getting #UNPARSEABLE for the below formula:

=SUMIFS(Value:Value, SubHeading:SubHeading, (CONTAINS("Service Contract", @cell), (Status:Status, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started")

Can someone help me figure out which part of the above formula is not correct?

Please ignore, I fixed it. I was missing the 2nd bracket after the CONTAINS section:

=SUMIFS(Value:Value, SubHeading:SubHeading, (CONTAINS("Service Contract", @cell)), Status:Status, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"))

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle Maas

    I'm glad you were able to fix this!

    Just a note, you don't actually need the extra parentheses around CONTAINS, if it's causing trouble:

    =SUMIFS(Value:Value, SubHeading:SubHeading, CONTAINS("Service Contract", @cell), Status:Status, OR(@cell = "Projected", @cell = "Schedule", @cell = "Not Started"))


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!