SUMIFS Formula won't compile

SUMIFS Formula won't compile

rclericorclerico
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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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")

  • 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.

     

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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

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

    yep

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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 NewcomePaul 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".

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

    Excellent!

    Happy to help!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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

  • Paul NewcomePaul 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).

Sign In or Register to comment.