Calculating Mode, Min with a criteria

[smalik]

The formula in M15 calculates the mode of the month based on the criteria of Policy ID. Data source is in B4:B43

I am getting the desired results using the Array formula. I am not a big fan of Array formulas as they hog up space and slowed down the calculations when the data set is too big. Is there a way to get the desired results without using an Array formula?

Formulas in M, N, & O are similar array formulas. Please Ignore L3:P9 as here I was calculating the values manually to see if my array formula was was working correctly.

Any help is greatly appreciated.

Best.

 

[jasonb75]

If you're using excel 2019 (or newer) then you could possibly use MINIFS and MAXIFS instead of MIN and MAX arrays.
There is no non array alternative for mode with criteria.

 

[smalik]

Thank You.