SUMIF(CONTAINS...

Options
Marilu M
Marilu M ✭✭✭
edited 07/06/22 in Formulas and Functions

I'm trying to gather info from another sheet but keep getting the "Unparseable" error message. Basically, I want to add the total amounts of any rows that contain "1st" as the district. The column that has the district information is a multi-select value hence why the contains. The current formula is set as:

=SUMIF( CONTAINS("1st"{FY22 NAI Funding Application Range 2})), {FY22 NAI Funding Application Range 3}

Any assistance would be appreciated.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 02/17/22 Answer ✓
    Options

    Hi @Marilu M

    If you're looking in a multi-select column for a specific value, I would actually use HAS Function instead. It looks for a direct match.

    You'll want to first list the column/range, then use @cell as the second range within your HAS function. This is so that it looks into each cell of that column to see if it has that value.

    Try this:

    =SUMIF({FY22 NAI Funding Application Range 1}, HAS(@cell, "1st"), {FY22 NAI Funding Application Range 2})

    Cheers!

    Genevieve

Answers

  • Marilu M
    Marilu M ✭✭✭
    Options

    I tried this version, and I am getting 0:


    =SUMIF(CONTAINS("1st", {FY22 NAI Funding Application Range 1}), {FY22 NAI Funding Application Range 2})

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 02/17/22 Answer ✓
    Options

    Hi @Marilu M

    If you're looking in a multi-select column for a specific value, I would actually use HAS Function instead. It looks for a direct match.

    You'll want to first list the column/range, then use @cell as the second range within your HAS function. This is so that it looks into each cell of that column to see if it has that value.

    Try this:

    =SUMIF({FY22 NAI Funding Application Range 1}, HAS(@cell, "1st"), {FY22 NAI Funding Application Range 2})

    Cheers!

    Genevieve

  • Marilu M
    Marilu M ✭✭✭
    Options

    @Genevieve P. It worked!! Thanks so much!!!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem! 🙂

  • earl_bennett
    earl_bennett ✭✭✭✭✭✭
    Options

    @Genevieve P. I'm doing something very similar and it works when I hard code the value in the HAS statement. But, when referencing a value in a cell it doesn't.

    Using the example above: HAS(@cell, "1st") works, but HAS(@cell, [1]2) does not.


    Note... The value in cell [1]2 is populated with a cross sheet formula the capture the names of our PM's.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @earl_bennett

    When you say it doesn't work, are you receiving an error or an incorrect result?

  • earl_bennett
    earl_bennett ✭✭✭✭✭✭
    Options

    Sorry, @Genevieve P. ... Not sure why, but it appears to be working now. It wasn't throwing an error just not returning the expected result. So weird, but thank you!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @earl_bennett

    Glad to hear it's resolved! 🙂

  • SkiPatrolScott
    SkiPatrolScott ✭✭✭✭
    Options

    @Genevieve P. not sure if I am misunderstanding the use of HAS..

    =SUMIF(Route:Route, HAS(@cell, "GAZ"), [Total Used]:[Total Used])

    I am trying to use this formula to sum all entries in Total Used where GAZ is part of the route, GAZ EX (Mt Allen) GAZ EX (No Name).... but unless I type the entire word it comes back as 0, I tried contains as well, and got 0. There will be at least 3.. and I am sure I will need variations of this formula for other routes....

    On the same thread, I would like to sum all the entries that do not have GAZ as part of the route.


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @SkiPatrolScott

    HAS will only search for exact matches, so only rows that have "GAZ" selected specifically, without any other text in that same selection. This means searching for HAS(@cell, "GAZ") excludes "GAZ EX (No Name)"

    You would want to use CONTAINS in this instance, to see if the string contains GAZ along with other words. Try:

    =SUMIF(Route:Route, CONTAINS("GAZ", @cell), [Total Used]:[Total Used])


    Then I would suggest for your second formula, simply using SUM to get the entire column and subtract your previous formula from the total:

    =SUM([Total Used]:[Total Used]) - [SUM Hand Shots]#


    If the CONTAINS function still comes back as 0, try SUMming the column without any criteria:

    =SUM([Total Used]:[Total Used])

    This checks to see if the values in that column are being read as text or numerical. If this general SUM is 0, then the numbers are being seen as text and cannot be added together. Can you clarify if there's a formula in this column? Or are the numbers being input manually?

    Cheers,

    Genevieve

  • SkiPatrolScott
    SkiPatrolScott ✭✭✭✭
    Options

    thanks @Genevieve P. I think I was not placing the contains("Gaz", @cell) in the correct order, yes the formula is working now....

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Glad to hear it! 🙂

    Yes, HAS and CONTAINS have opposite syntax, haha. I've been caught by that before.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!