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

GMichal ✭✭✭✭
edited 04/17/24 in Formulas and Functions

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!