Selected Answer

I don't think you need any filters or VBA. In fact, you don't need the *Filtered* tab. You can have a better result, however, with better preparation. The concept I suggest is that you enter your filter criteria on the *Summary* sheet and use SUMIFS() functions to extract the totals from the *Data* sheet. When the data in the *Data* sheet change so does the summary. It's that easy - but not very easy to implement. That's why the first step should be to make it easier. The tool to use is the "Named range". Please read up about named ranges if you aren't familiar with them.

In the attached workbook I added a sheet *New Summary* which is a copy of your *Summary* sheet but with changes in it. First I added the rows 3:5 where cells C3:C5 contain three named ranges *Year*, *State* and *Status*. The purpose of naming these cells is simply becase "Year" is a better description than "$C$3". Your formulas will be easier to read.

In F8:F9 I explain how to create a dynamic named range. You need this because your data vary in size. The advantage in naming it is that you can refer to the range by the same name whatever its size might be. The range changes, the name stays the same, the formulas referring to it remain the same.

On the Ribbon's *Formulas* tab, click *Define Name*. In the dialog box that opens, enter the name at the top, a description in the middle and the definition of the range in the *Refers To* field at the bottom. The current selection is prefilled there but that is no use to you for dynamic ranges (good for the *Year*, *State* and *Status*, though). In essence you need a formula like this one:-

`[F8] =OFFSET(Data!$V$2,0,0,8,1)`

In essence, this formula tells Excel to select a range starting from V2 (on the *Data* tab), expand it by 0 rows and 0 columns, and then give it a height of 8 rows and a width of 1 column. That would end up being Data!$V$2:$V:9 - but not dynamic. The formula below finds the last used row in Data!A:A. This is important because I presume that column A will always have the maximum number of used rows. There is a -1 at the end because we start in row 2. The number of rows on the *Data* tab is the same for all columns. Therefore we can use the reading from column A for all column ranges.

```
[F9] =SUMPRODUCT(MAX(ROW(Data!$A:$A)*(Data!$A:$A<>"")))-1[CODE]
In the formula below the 8 was replaced with the formula in F9. Go to the
```*Names Manager*. You will find the formula from F10 in the *Refers To* field of the named range *Dates*. This range will now expand and shrink with the amount of data on the tab.
[CODE][F10] =OFFSET(Dates!$V$2,0,0,SUMPRODUCT(MAX(ROW(Data!$A:$A)*(Data!$A:$A<>"")))-1,1)

I set up named ranges *States*, *Statuses* and *Prices* as well. The formulas are in F11:F13 and all follow the same pattern. You will need more ranges but those I created are sufficient to try the SUMIFS formula. Here it is.

`[C8] =SUMIFS(Prices,Dates,">=" & DATE(Year,1,1),Dates,"<=" & DATE(Year,12,31),States,State,Statuses,Status)`

The syntax is SUMIFS(Sum_column, Criteria1_column, Criteria2, Criteria2_column, Criteria2, Criteria3_column, Criteria3, etc.etc.)

Applied: Sum the *Prices* column IF([Dates >= Jan_1_SelectedYear] AND [Dates <= Dec_31_SelectedYear] AND [States = SelectedState] AND [Statuses = SelectedStatus])

For your other fields the formula remains essentially the same for all your totals except for the sum_column for which you would have to set up the ranges first.