# Formula Error

Options
✭✭✭✭

I want to create a formula that returns the median value when the criteria is met and "0" when the criteria is not met (no data). I used IFERROR for this purpose, however I received a #INCORRECT ARGUMENT SET error. Can someone assist me in fixing this formula? I appreciate your help.

This is the formula for your reference: =IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0)

• ✭✭✭✭✭✭
Options

I would think the IFERROR would take care of it. Lets try this:

=IF(ISERROR(IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0)), 0, IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0))

• ✭✭✭✭✭✭
Options

Syntax looks fine, and that particular error is typically indicating an issue with your ranges. Double check that they are all referencing single columns.

When setting up a cross sheet reference, always give the sheet time to load completely in the creation window. Once it loads completely, the selection automatically reverts back to the "home cell" which is the cell in the top left corner of the sheet. If you select your range before the sheet loads completely, you may not notice that it has shifted. The best indicator that the sheet has loaded completely in the window is the sheet name will appear in bold just below the cross sheet reference name.

• ✭✭✭✭
Options

Hi Paul,

Thank you for your response! I tried what you said, but the issue still persists. The error occurs only when the COLLECT function fails to gather any values from the range because no rows meet the specified criteria.

• ✭✭✭✭✭✭
Options

Ah. That information helps.

And you still get the error even with the IFERROR statement?

• ✭✭✭✭
Options

Hi Paul,

Yes. Any other ideas on how to fix this error?

• ✭✭✭✭✭✭
Options

I would think the IFERROR would take care of it. Lets try this:

=IF(ISERROR(IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0)), 0, IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0))

• ✭✭✭✭
Options

Hi Paul,

It worked! Thank you so much!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!