SUMIF Not Working?

Jessb9187 ✭✭✭✭
edited 12/09/19 in Smartsheet Basics

Hello again,

I am using a pretty basic formula which I've used countless times, even yesterday, and now I'm getting an #unparseable error. 

I need to add the number of onsite days (range) if the visit type (criterion) is listed as a conversion. My formula looks like this:

=SUMIF({KPI: Visit Types &Days Onsite Remainder 20 Range 1}, {KPI: Visit Types and Days Onsite Range 1} "Conversion")

I'm not sure what is wrong with this formula. Any ideas on this one?


  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭
    edited 06/06/19

    Have you tried using [ over {? 

    If you share your Smartsheet to me at I'm happy to take a look at it. Your formula doesn't make a lot of sense. There's no commas fragmenting what it is to look for in the criteria.



    Dr. St Nicholas Burrus DHA, PMP

    I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/06/19

    Switching the curly brackets to square brackets will not work. Square brackets are used for column names that have a number, space, and/or a special character. Curly brackets are used for cross sheet references.


    It looks to me like two different sheets are being referenced. Is that correct?


    You are also missing a comma between the criteria range and the criteria.

  • Jessb9187
    Jessb9187 ✭✭✭✭

    It was the same sheet. I'm not sure why the reference for the criteria was pulling a different name. I've updated my formula and now it's pulling the same sheet name at least. This one, gives me an incorrect argument set.

    =SUMIF({KPI: Visit Types &Days Onsite Remainder 20 Range 2}, {KPI: Visit Types &Days Onsite Remainder 20 Range 1}, "Conversion")


  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi - I think your order of arguments is incorrect in the last example...Syntax for a SUMIF function is


    In the example above it appears to be in the order of


    Try reordering your arguments to match this instead:

    =SUMIF({KPI: Visit Types &Days Onsite Remainder 20 Range 1}, "Conversion",{KPI: Visit Types &Days Onsite Remainder 20 Range 2})

    Hope this helps...

    Kind regards

    Debbie Sawyer Consultant & Training Manager



  • Jessb9187
    Jessb9187 ✭✭✭✭

    So I figured it out. Apparently you need a SUMIFS not a SUMIF formula. Updated formula: 

    =SUMIFS({KPI: Visit Types &Days Onsite Remainder 20 Range 2}, {KPI: Visit Types &Days Onsite Remainder 20 Range 1}, "Conversion")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your syntax is correct. here are a few things to look at:


    1. Click into the formula, click into the middle of the range reference, and then select "Edit Range". Give it a minute to load up.


    Sometimes when I go to reference another sheet, if I select my range too quickly it will be before the sheet has had a chance to truly load and the range reverts to the top left cell.


    2. Both ranges need to be the same size. Since you are referencing another sheet, you should be able to select the entire column by clicking on the column header when establishing the range.


    3. Make sure your ranges are in the right order within your formula. The first range is the one you want to SUM, and the second range should be the range that contains the cells that would be housing your criteria.


    Your overall syntax (without being able to see the ranges themselves) is correct though. Parenthesis, commas, quotes, etc. are all where they should be.


    Based on the incorrect argument error, my first guess would be the range sizes not matching (#2 which could be a result of #1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ugh! Good catch on the syntax. I use SUMIFS exclusively and was getting it completely backwards. ?