SUMIFS Formula won't compile

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

Find more posts tagged with

Formulas

Community Platform

Comments

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å

yep

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

Sign in to join the conversation!

Quick Links

Trending discussions

index match using multiple critieria
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
Auto Check a box after 24 hours
I have tried to get smartsheet to auto check a box 24 hours after a row is created and column "Task Started" is not checked. =IF(AND([Task Started]@row <> 1, TODAY() - Created@row >= 1), 1) This returns #UNPARSEABLE. Any help would be appreciated.