Working with advanced filters...?

[BillJo]

I created an advanced filter in my worksheet titled Shipping Data, and I am trying to create a function in B7 that will average the field listed in B6 for the ShipData_All (which is one of my name ranges) database but using the criteria range A3:J4.

I haven't worked with filters as much and any help is appreciated.

[FDibbins]

I think you have your references all mixed up?
B7 contains a SUM (not an average)
B6 contains an average (not a field)
A3:K4 are headings with only 1 entry (E4)

From the sounds of it though, you probably need to look at the SUMIFs and AVERAGEIFs() functions

[BillJo]

The reason why I have a SUM in B7 is because I wanted to create a function that would find the sum of the filtered data based upon the field name listed in B5 which is Qty. And the reason why I have an AVERAGE in B6 id I wanted to create a function that will find the average of the filtered data based upon the field again in B5 which is Qty. If that makes sense.

So going back to my original question, I think I may have to average an average, but I don't know how to do this?

[FDibbins]

For a start off, an average of an average is mathematically incorrect.

you said you want to filter on QTY, but filter what? You have 100 entries, with an average of 267.5, a min of 75 and a max of 500. What do you want to filter on?

Im sorry, but I totally do not understand what you want Perhaps if you gave some sample answers and explained how you got them?

[BillJo]

Ok so I set up a calculation on the filtered data based on one of three data fields in cell B5, in B6 I created a function that will find the average of the filtered data based on the field name listed in B5. In B7, I created a function that will find the sum of the filtered data based on the field name listed in B5.

Since, on my Shipping Report sheet the database statistics need to either be created or corrected so I set up a criteria range starting in cell A3, in cell E4 I typed CA as a criterion for that State and now in B7 I want to create a function that will average the field listed in B6 for the ShipData_All database using the criteria range in A3:J4.

[FDibbins]

hmm ok, maybe this...
=AVERAGE(INDIRECT(B5))

Or, if you need to include additional criteria...
=AVERAGEIFS(INDIRECT($B$5),State,$E$4)

[BillJo]

Thank you, but I just realized that I would have to create a function that will average the field listed in B6 for the ShipData_All database using the criteria range in A3:J4 in cell B7 in my worksheet titled Shipping Report. So do you think I can incorporate what you stated above into B7 in my worksheet Shipping Report?

[FDibbins]

I have added 2 more tables to your sheet1 (with yellow hi-light) and moved yourtable to the right for comparison

the 1st table is by State, with a Drop-down of available states. It pulls only those cities in that state. There is a tiny hiccup, it starts pulling into the 2nd row of the table, unless IL is selected, then it skips a row

The 2nd table pulls all unique city names, but has no link to states. I could probably add this