# Thread: Min, Max, Median & Average cells based on other cell's criteria

1. ## Min, Max, Median & Average cells based on other cell's criteria

I'm trying to find the statistics for the current rent based on the type and size of property in the attached worksheet. I know I can use sumif and averageif, but no such functions exist for min and max. Therefore I've used the same basic formula with variations for the statistic needed for all four statistics:

=MIN(IF((SW_DW=\$R\$8),Current_Rent))

\$R\$8 is populated by a pull down menu of the unique values in the SW_DW named range. I'm using the pull downs since I want to be able to change what type of property (SW_DW) or what size (Size) for the columns in the table.

The values are not being calculated for all cells and they don't reliably recalculate when I change the entry in the pull down menu above the summary table. A picture of the worksheet and the excel file are linked below. Rent Roll.xlsx

Anyone have any ideas?  Reply With Quote

2. Use this array formula to ignore blanks in the rent

=MIN(IF((SW_DW=R\$8)*(Current_Rent<>""),Current_Rent))  Reply With Quote

criteria, if statements, pull down, summary formula 