Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Adding One More Criteria to Working COLLECT Formula

✭✭✭✭✭
edited 02/25/25 in Formulas and Functions

I have a working formula where I need to add one more criteria to a COLLECT function but can't seem to get it working. Here is the current working formula:

=MEDIAN(COLLECT({Platform Test - Submit Status to Intake Date}, {Platform Test - Quote Testing Completed Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Platform Test - New or Existing}, @cell = "Existing"))

What I am trying to do is add one more criteria to this formula based upon the multi-select content of a cell as shown below:

In the above screenshot of the cell that has the criteria that I need to add to the formula to act as a filter of the data, I selected all 4 options from the dropdown list. Here is what I have come up with so far but I am getting an #INCORRECT ARGUMENT response and can't seem to identify the issue:

=MEDIAN(COLLECT({Platform Test - Submit Status to Intake Date}, {Platform Test - Quote Testing Completed Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Platform Test - New or Existing}, @cell = "Existing", {Platform Testing - Issue Type}, @cell = [Issues Type]1))

What am I missing?

Tags:

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions