#INCORRECT ARGUMENT SET

Shubha

Hi Team,

I am trying to fix this formula but it keeps giving me an error #INCORRECT ARGUMENT SET

Could you please help me fix this one it's kind of urgent to close.

=IF([Calculation of Receivd to Warehouse]@row >= 31, "Delayed", IF([Calculation of Receivd to Warehouse]@row <= 30, "Expected next 30 Days", "Green", IF([Calculation of Receivd to Warehouse]@row <= 0, "To Warehouse next 30Days", "Yellow")))

Brian_Richardson

@Shubha couple of things going on... you're missing a , after "Yellow", but also you still have too many arguments in the function. IF works with criteria,true result,false result. You have criteria, true result,false result,something else.

It also looks to me like you're trying to set "Expected next 30 Days" or "To Warehouse" etc in one cell, and a color status "Red", "Yellow" etc in a different cell? You cannot do that with a single formula. You need a formula in each column that you're trying to set.

Also nested IF statements check conditions in the order that you list them, and when they are true then the whole formula stops there. So by doing <30 and then <0 you're never going to see the <0 part as anything <0 is also <30 which is checked first.

So in the cell where you want the status words, use a formula like:

IF([Calculation of Receivd to Warehouse]@row >= 31, "Delayed", IF([Calculation of Receivd to Warehouse]@row <= 0, "To Warehouse next 30Days", IF([Calculation of Receivd to Warehouse]@row <= 30, "Expected next 30 Days")))

Then in the color cell use a formula like:

IF([Calculation of Receivd to Warehouse]@row >= 31, "Red", IF([Calculation of Receivd to Warehouse]@row <= 0, "Green", IF([Calculation of Receivd to Warehouse]@row <= 30, "Yellow")))

Brian_Richardson

@Shubha #INCORRECT ARGUMENT SET means you have one too many arguments in a function. In other words, one of your IF statements has too many options in it. It's the "Expected next 30 Days", "Green", that's messing you up.

IF takes three arguments IF (test, result if true, result if false). When nesting IF statements usually it's in the "result if false" part. Your second IF statement instead has IF(test, result if true, result if false, another IF). You need to move the "Green" part to another IF statement.

Shubha

Hi @Brian_Richardson.

I tried using your instructions & now getting #UNPARSEABLE error. Not able to fix this one, if you could help me in it, that would be really helpful.

IF([Calculation of Receivd to Warehouse]@row >= 31, "Delayed", IF([Calculation of Receivd to Warehouse]@row <= 30, "Expected next 30 Days", "Yellow" IF([Calculation of Receivd to Warehouse]@row <= 0, "To Warehouse next 30Days" ,"Green")))

Brian_Richardson

@Shubha couple of things going on... you're missing a , after "Yellow", but also you still have too many arguments in the function. IF works with criteria,true result,false result. You have criteria, true result,false result,something else.

It also looks to me like you're trying to set "Expected next 30 Days" or "To Warehouse" etc in one cell, and a color status "Red", "Yellow" etc in a different cell? You cannot do that with a single formula. You need a formula in each column that you're trying to set.

Also nested IF statements check conditions in the order that you list them, and when they are true then the whole formula stops there. So by doing <30 and then <0 you're never going to see the <0 part as anything <0 is also <30 which is checked first.

So in the cell where you want the status words, use a formula like:

IF([Calculation of Receivd to Warehouse]@row >= 31, "Delayed", IF([Calculation of Receivd to Warehouse]@row <= 0, "To Warehouse next 30Days", IF([Calculation of Receivd to Warehouse]@row <= 30, "Expected next 30 Days")))

Then in the color cell use a formula like:

IF([Calculation of Receivd to Warehouse]@row >= 31, "Red", IF([Calculation of Receivd to Warehouse]@row <= 0, "Green", IF([Calculation of Receivd to Warehouse]@row <= 30, "Yellow")))

Shubha

That works thanks @Brian_Richardson

WinaHath

getting the same error message on this formula:

=INDEX(COLLECT({Approved By}, {Department Code}, [Department Code]@row, {Approval Sequence}, 1, 0))

Please help!

Brian_Richardson

Hi @WinaHath you have the same problem as Shubha did. You have too many arguments in your Collect statement. That's what that error means. Are you trying to find the first match for the Department Code in another sheet, where Approval Sequence=1?

If so, then don't use ,0 anywhere. I'm going to assume you want the first match in the Collect, so try

=INDEX(COLLECT({Approved By}, {Department Code}, [Department Code]@row, {Approval Sequence}, 1),1)

WinaHath

@Brian_ColumbiaSPS

Thank you! I will try that!

Mark King

I'm having this issue as well - getting incorrect argument set - but can't find the issue:

=SUMIFS({NON_STOCK_AGING_DOLLARS_HISTORY Range 5}, {NON_STOCK_AGING_DOLLARS_HISTORY Range 2}, WHS@row, {NON_STOCK_AGING_DOLLARS_HISTORY Range 3}, [Month2]@row)

If I just do =SUMIFS({NON_STOCK_AGING_DOLLARS_HISTORY Range 5}, {NON_STOCK_AGING_DOLLARS_HISTORY Range 2}, WHS@row) it works. but adding a second criteria argument and it falls apart.

Brian_Richardson

@Mark King that looks ok to me so the problem is probably in your ranges. They have to be exactly the same set of rows from the same sheet, just different columns. Otherwise it’s impossible for SUMIFS to apply both criteria if the ranges vary.

KParker87

@Brian_Richardson I am also having trouble. I am making a helper column that will identify cells that A) are not blank and B) are more than a year in the past.

=IF(AND(NOT(ISBLANK([First Date of Suspension for 2023-2024]1)), [First Date of Suspension for 2023-2024]1 < TODAY(-365), "1", "0"))

Bonus points if you know how to attribute this formula to a checkbox column type rather than just having 1's and 0's as a result.

Brian_Richardson

It looks like your just missing a closing parenthesis for the AND…you had it at the end of the if statement by accident.

For checkbox use true and false. You don’t have to declare “else 0” in your if because checkboxes default to false.

=IF(AND(NOT(ISBLANK([First Date of Suspension for 2023-2024]1)), [First Date of Suspension for 2023-2024]1 < TODAY(-365)), true)

vishalpandey

Hello @Brian_Richardson,

I am having the same issue.

I am using the below formula on a column to populate a value.

=IF(CONTAINS("507737 - Rachel Hawley", [Fellow 1 BIN]@row, [Fellow 1 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 2 BIN]@row, [Fellow 2 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 3 BIN]@row, [Fellow 3 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 4 Bin]@row, [Fellow 4 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 5 BIN]@row, [Fellow 5 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 6 BIN]@row, [Fellow 6 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 7 BIN]@row, [Fellow 7 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 8 BIN]@row, [Fellow 8 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 9 BIN]@row, [Fellow 9 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 10 BIN]@row, [Fellow 10 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 11 BIN]@row, [Fellow 11 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 12 BIN]@row, [Fellow 12 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 13 BIN]@row, [Fellow 13 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 14 BIN]@row, [Fellow 14 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 15 BIN]@row, [Fellow 15 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 16 BIN]@row, [Fellow 16 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 17 BIN]@row, [Fellow 17 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 18 BIN]@row, [Fellow 18 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 19 BIN]@row, [Fellow 19 $ Share]@row, IF(CONTAINS("507737 - Rachel Hawley", [Fellow 20 BIN]@row, [Fellow 20 $ Share]@row, "Enter Amount"))))))))))))))))))))))))))))))))))))))))

I am using the above formula to copy the value of respective Fellow Share to a Row if their name is on that Fellow Bin.

For Example If 507737- Rachel Hawley appears on the Fellow 9 column, I want the value of Fellow 9 Share to be copied to one other column where I am entering this formula.

It gives me an INCORRECT ARGUMENT SET as well. Is there anything I need to do differently here?

Brian_Richardson

Your IF statements have too many arguments. IF is IF(logic,true result,false result) but you have IF(logic,true,false,next IF). Hence the error.

You need to put the next IF statement in the false part of the IF arguments like this: IF(logic,true,next IF)

Brian_Richardson

Also just a suggestion, if you have a large number of names to check for and results, you may want to setup a lookup sheet instead and use INDEX/MATCH to bring in the results that you want.

heyjay

https://community.smartsheet.com/discussion/comment/447584#Comment_447584

I think you need to close all your CONTAINS function with ).