Adding One More Criteria to Working COLLECT Formula

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?
Best Answers
-
@cell = [Issues Type]1
would change to
HAS(@cell, [Issues Type]1)
.
-
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.
Answers
-
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.
-
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.
-
@cell = [Issues Type]1
would change to
HAS(@cell, [Issues Type]1)
.
-
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)))
-
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.
-
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.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!