Adding One More Criteria to Working COLLECT Formula

GMichal

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?

Paul Newcome

@cell = [Issues Type]1

would change to

HAS(@cell, [Issues Type]1)

.

Paul Newcome

Double check your cross sheet references are all of the same size and shape. Sometimes things can be a little slow on the back-end and change a full column reference to a single cell reference if you are moving a little too fast.

Paul Newcome

Double check all of your ranges are single columns. Sometimes things can move a little slow on the back-end when creating cross sheet references which in turn ends up referencing what you THOUGHT was a full column but ended up being a single cell.

GMichal

Thanks, @Paul Newcome. I double-checked the range on the additional criteria ({Platform Testing - Issue Type}) and it is a single column. I have a question on the dropdown list that you may be able to answer. The cell reference (@cell = [Issues Type]1) that I am calling out in the newly added portion of the formula is set to allow for multiple selections as I want the user to be able to select one or all of the dropdown list choices; acting like a filter of the data. Did I define the cell reference properly for this type of response?

As mentioned previously, the current formula is working fine; I just need to add another filter criteria to it. For what it's worth, here is a screenshot of the dashboard where the date range and issue type filters are input:

The 'Under Construction' widgets are the new additions to the dashboard. The Issue Type selection, in this case, is looking for 4 different values in the Issue Type column (Change Order, Dealer Quoting Error, New Quote, PAR Issue). These values are successfully transferring to the cell reference (@cell = [Issues Type]1) which is where I am trying to input them into the newly added portion of the formula.

Appreciate any insight I can get to make this work in my formula.

Paul Newcome

@cell = [Issues Type]1

would change to

HAS(@cell, [Issues Type]1)

.

GMichal

Still got an #INCORRECT ARGUMENT SET error. Making the recommended change gave me this 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", {Platform Test - Issue Type}, HAS(@cell, [Issues Type]1)))

Paul Newcome

Double check your cross sheet references are all of the same size and shape. Sometimes things can be a little slow on the back-end and change a full column reference to a single cell reference if you are moving a little too fast.

GMichal

I checked the subset of the data that I was analyzing in the main sheet and found no matches which was driving the error message. Thanks for walking me thru to successfully adding the additional criteria.

Paul Newcome

Happy to help. 👍️