# SUMIFS formula returning #INCORRECT ARGUMENT SET: order of Range & Criteria?

Options
edited 06/11/20

Hello,

I cannot figure out why this SUMIFS is not working. When I break up the SUMIF criteria, they both work fine. But when I put them together, I get #INCORRECT ARGUMENT SET error message.

=SUMIFS({MH Client Profiles v2 Range 13}, "East", {MH Client Profiles v2 Range 16}, 1, {MH Client Profiles v2 Range 3})

All I want is to validate that the Region column has "East" and the Terminated column is "checked" and then add up the Revenue column.

Thank you!

«1

• Options

Perfect, this fixed my formula. I got the formula switched...criteria was in the front and revenue was in the back. Thanks a lot!

• ✭✭✭✭✭✭
Options

According to Smartsheets... you will want to put the range to sum first... Then the 1st criterion range, then the Criteria. Try reorganizing your formula to put the range you want to sum first. Assuming MH Client Profiles V2 Range 3 is the sum reange, it would look like this:

=SUMIFS({MH Client Profiles v2 Range 3}, {MH Client Profiles v2 Range 13}, "East", {MH Client Profiles v2 Range 16}, 1)

• Options

So with SUMIF, the criteria goes first and then the range to sum last. Is that true?

• ✭✭✭✭✭✭
Options

Yes, if you use SUMIF, its reversed --- a little confusing in my book. I tend to always use COUNTIFS, or SUMIFS, because its always likely you may add a criterion and SUMIFS works with one or many...

• Options

Thanks Mike!

• ✭✭✭✭✭✭
Options

Absolutely! Anytime.

• Options

Hi Everyone, I am receiving the same error for my formula:

=COUNTIF([Assigned To]:[Assigned To], "Akil", Status:Status, "Complete")

Can someone help me? I want to look at two columns to see how many tasks Akil has completed. The formula is identifying the columns but I don't know

what is causing the "Incorrect argument set" error.

• ✭✭✭✭✭✭
Options

Hi Jenn,

You need to change the formula to a COUNTIFS instead.

Did that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

LOL! I added an "s" and it worked. What a simple solution! I was wracking my brain like "why doesn't this work!?"

Thanks Andrée! Works great now.

• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• edited 01/10/20
Options

Hi Andrée,

What if I wanted to have multiple options that I want to include in my count? How to I add "or" in my formula?

=COUNTIFS([Assigned To]:[Assigned To], "Akil", Status:Status, "New/Not Started")

^ this works, but say I want to count if Assigned to Akil, and the Status is either "New/Not Started" or "Complete"?

• ✭✭✭✭✭✭
Options

Try this.

```=COUNTIFS([Assigned To]:[Assigned To], "Akil", Status:Status,
OR(@cell = "New/Not Started", @cell = "Complete"))
```

Did it work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Amazing! That worked great! Thanks again Andrée :)

• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!