SUMIFS Formula won't compile

rclerico
rclerico ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hey there community! I have been working on this formula for a few days now and it just won't fly.  I think the "<>Closed" is what is throwing it off?  I have tried all sorts of varieties of syntax (<>"Closed", =2019,="2019",) - and it wont fly.

Here is what I have now.  Please see screen shot as well.

=SUMIFS([$USD - Sales Potential]:[$USD - Sales Potential], 2019, [Estimated PO Year]:[Estimated PO Year], "<>Closed", Status:Status)  --  Error thrown: #INCORRECT ARGUMENT SET

Thanks in advance!

Formula Screenshot.png

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Try this.

    =SUMIFS([$USD - Sales Potential]:[$USD - Sales Potential], 2019, [Estimated PO Year]:[Estimated PO Year], <>"Closed", Status:Status)

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    This was at the wrong position: 

    =SUMIFS([$USD - Sales Potential]:[$USD - Sales Potential], 2019, [Estimated PO Year]:[Estimated PO Year], "<>Closed", Status:Status)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree, I think you are replicating the main issue. The syntax is reversed after the range_to_sum portion. Instead of going

     

    =SUMIFS(range_to_sum, criteria_range_1, criteria_1, criteria_range_2, criteria_2)

     

    the formula is going 

     

    =SUMIFS(range_to_sum, criteria_1, criteria_range_1, criteria_2, criteria_range_2)

    .

    Notice the criteria coming before the range?

    .

    I think this should work...

    =SUMIFS([$USD - Sales Potential]:[$USD - Sales Potential], [Estimated PO Year]:[Estimated PO Year], 2019, Status:Status, <> "Closed")

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Paul,

    Thanks!

    I just saw that error and thought that might be it. I was short for time so I couldn't look more thoroughly.

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No worries. You've done the same for me in the past, and what really matters is that a solution is found for the issue. yes

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    yep

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • rclerico
    rclerico ✭✭✭✭✭✭

    It worked! Thanks you guys!  You ROCK! Sorry about the delayed response, I thought I would get an email or notification of some sort when people posted responses.  Anyone know how to turn that on? 

    Thanks again!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    Notifications should be "On" by default. One of 3 things could have happened.

     

    1. You are the victim of a glitch.

    2. You clicked on the link at the bottom of an email to Unsubscribe from Community notifications in general.

    3. You clicked on the link within the thread to "Unfollow this discussion".

    .

    For 1 or 2, you will want to contact support.

    For #3, just scroll up to the original post and click the link that says "Follow this discussion".

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • rclerico
    rclerico ✭✭✭✭✭✭

    Darn.  I think it's #1 or #2.  I scrolled up and the only option I had was to unfollow.  Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. I would definitely reach out to support to see what steps need to be taken to start receiving notifications again (if you want them haha).

    thinkspi.com