SUMIFS Formula won't compile

rclerico

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!

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

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)

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

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.

 

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. 

Andrée Starå

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

Happy to help! 

 

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å

Excellent!

Happy to help!

rclerico

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

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