COUNTIFS Function of # of Values Used in a Median Calculation for Metric Sheet

GMichal

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.

Courtney S.

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:

=COUNT(DISTINCT(COLLECT formula — Smartsheet Community

COUNT(COLLECT) assistance please — Smartsheet Community

Courtney S.

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:

=COUNT(DISTINCT(COLLECT formula — Smartsheet Community

COUNT(COLLECT) assistance please — Smartsheet Community

GMichal

Thank you. I had a block on this one...trying to make it more difficult than it really was.