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

Amanda Chu

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.

Any advice would be appreciated. 

Thank you!

Nic Larsen

This should be the layout.

=SUMIFS({Revenue:Revenue}, {Region:Region}, ="East", {Terminated:Terminated}, 1)

Nic Larsen

This should be the layout.

=SUMIFS({Revenue:Revenue}, {Region:Region}, ="East", {Terminated:Terminated}, 1)

Amanda Chu

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

Mike Wilday

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)

• https://help.smartsheet.com/function/sumifs

Amanda Chu

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

Mike Wilday

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

Amanda Chu

Thanks Mike!

Mike Wilday

Absolutely! Anytime. 

Jenn Pestalozzi

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.

Andrée Starå

Hi Jenn,

You need to change the formula to a COUNTIFS instead.

More info:

https://help.smartsheet.com/function/countifs

Did that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jenn Pestalozzi

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.

Andrée Starå

Excellent!

Happy to help!

Jenn Pestalozzi

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

Andrée Starå

@Jenn Pestalozzi

Try this.

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

Did it work?

Jenn Pestalozzi

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

Andrée Starå

@Jenn Pestalozzi

Excellent! 

Happy to help!