COUNTIFS Function of # of Values Used in a Median Calculation for Metric Sheet
I have a formula in a metrics sheet where I am successfully calculating the median of values that meet a specific date range:
=MEDIAN(COLLECT({Resi - Financial Approval Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1)))
I now want to calculate in a adjacent column the total number of values that were used to calculate the median in the first column. I am assuming to use the same cell references (ranges) to the other sheet and I need to reference the same date range but I can't come up with anything that works. AI came up with:
=COUNTIFS({Resi - Financial Approval Duration}, <>"", {Resi - Enter Stage 7 Date}, @cell >= $[Start Date]$1, [2023 Q2 Total]:[2023 Q2 Total], @cell <= $[End Date]$1)
but that didn't work. Any ideas?
These will be used to feed a dashboard.
Best Answer
-
Since you already have a working formula with COLLECT and you want to count the values in the range the COLLECT made, maybe look into just doing COUNT + COLLECT rather than making a COUNTIF equivalent of the COLLECT? These posts might be useful to you to look at:
Answers
-
Since you already have a working formula with COLLECT and you want to count the values in the range the COLLECT made, maybe look into just doing COUNT + COLLECT rather than making a COUNTIF equivalent of the COLLECT? These posts might be useful to you to look at:
-
Thank you. I had a block on this one...trying to make it more difficult than it really was.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!