Create UI to filter and aggregate in Excel

Question

I have a long excel containing dates and many value columns. I would want to create a dynamic filter where the user can input a date range in two cells and the values from each column will be filtered by the date range and an aggregated value calculated from the filtered values will be shown.

For example in the table:

Row year    weight_pounds   gestation_weeks  
1   2005    6.0627122049999995  38   
2   2006    7.12534030784   40   
3   2007    7.4406013425    39   
4   2008    3.30032006214   43   
5   2009    7.1099079495    41   
6   2010    7.31273323054   40   
7   2010    9.31232594688   42

I want to filter by year 2007 to 2009 and show the average on both value columns in the sheet.

How can I approach this in excel? I have no experience to VBA but I am open to VBA if it is required.

Have you considered turning your table into a pivot table? The date column can then be used as a pivot table filter. Or is it essential that the start and end dates are typed into two separate cells? — MattClarke
@MattClarke, yes the start and end date have to be typed into two cells — hangc

MattClarke MattClarke · Accepted Answer · 2017-03-05T23:50:30

Define a helper column that tests whether the row is within the required date range and filter based on that column.

Suppose the start date is in X1 and end date is in X2. And suppose your main data table starts with the heading Row in A1.

Then the formula in D2 might be something like =and(B2>=X1,B2<=X2)
Copy that formula down the whole list to give a column with TRUE and FALSE values
Turn on filters on the heading row
Select just TRUE in the helper column
For the average you can either use =SUBTOTAL(101,C:C) (the 101 parameter says to give the average of the rows that are still visible) or =AVERAGEIFS(C:C,B:B,">="&X1,B:B,"<="&X2) (which works regardless of how the main data table has been filtered.

Create UI to filter and aggregate in Excel

1 Answers