What am I doing wrong?

Nathan Slatton

Shows #Incorrect Argument set

=IF(AND(IF([Jan Scheduling Location]@row = "BPHO|Belen Public Health Office", [Jan Appt Type]@row, "Err1"), IFERROR(INDEX(DISTINCT(COLLECT([BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], [BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], NOT(ISBLANK(@cell )))), Index@row), "err2"), "err3"))

Kelly Moore

@Nathan Slatton

A report might do this for you, depending on your sheet structure. You would Group by Location then by Appt Type, counting the Apt Types

As Paul and I are indicating, your IF/AND is not syntaxed correctly. As you have it written, there is no 'AND' portion of the formula, that is two simultaneous criteria that must be met for the IF statement to be true.

Try the INDEX/COLLECT without the IF statements but include the location in the COLLECT.

IFERROR(INDEX(DISTINCT(COLLECT([BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], [Jan Scheduling Location]:[Jan Scheduling Location],"BPHO|Belen Public Health Office", [BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], <>"")), Index@row),"err3")

Does this give you the correct list?

Kelly

Kelly Moore

Hey @Nathan Slatton

If your INDEX/COLLECT is part of the IF/AND function of your formula, you need to indicate what the INDEX/COLLECT is equal to. If the INDEX/COLLECT function is not part of the IF/AND section of the formula then the IF/AND needs to be edited. Help me understand, in words, what you were trying to accomplish.

Kelly

Paul Newcome

It the last comma in the AND function.

You have

AND(this = that, something, other)

You should have

AND(this = that, something = other)

Nathan Slatton

https://community.smartsheet.com/discussion/comment/481759#Comment_481759

I am trying to have it check against a list (Multiple duplicates) for a specific location and then have it pull all the distinct appt types for that location. I can do this with 2 seperate columns but am trying to do it with 1.

Kelly Moore

@Nathan Slatton

A report might do this for you, depending on your sheet structure. You would Group by Location then by Appt Type, counting the Apt Types

As Paul and I are indicating, your IF/AND is not syntaxed correctly. As you have it written, there is no 'AND' portion of the formula, that is two simultaneous criteria that must be met for the IF statement to be true.

Try the INDEX/COLLECT without the IF statements but include the location in the COLLECT.

IFERROR(INDEX(DISTINCT(COLLECT([BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], [Jan Scheduling Location]:[Jan Scheduling Location],"BPHO|Belen Public Health Office", [BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], <>"")), Index@row),"err3")

Does this give you the correct list?

Kelly

Nathan Slatton

https://community.smartsheet.com/discussion/comment/481815#Comment_481815

That Worked!! Thank you.