Need to average values of cells between two dates, and based on text field

[dallas1593]

Good afternoon! I am trying desperately (via averageif/averageifs, and combos of others) to average the cells in a column with two criteria: must be between two dates (a week apart), and must only average cells based on a text value (in this example I gave "X").

So below, I have two tables, one for item "X" and the other item "Y". In the column "Avg. Days to Build" for both, I want to average the cells in a seperate tab (in column A), based on the dates in the "Date" column below, and based on the table header (X or Y).

So for January 8th, I'd like to average all cells between 1/1/17 and 1/1/17 that are item X, and in the next table do the same but for item Y.

Is this possible? I also would like this formula dynamic, because I have a macro that auto-adds a new row for the upcoming week (so every friday, I'll hit it, and it adds the most recent weeks' data).

I am completely stumped!

X
Date	Units Needed	Units Built	Percentage Built	Avg. Days to Build
1/1/2017	12	10	83%
1/8/2017	10	7	70%
1/15/2017	14	9	64%
1/22/2017	13	11	85%
1/29/2017	10	9	90%

<tbody>
</tbody><colgroup><col><col span="2"><col><col></colgroup>

Y
Date	Units Needed	Units Built	Percentage Built	Avg. Days to Build
1/1/2017	10	9	90%
1/8/2017	13	7	54%
1/15/2017	14	9	64%
1/22/2017	10	11	110%
1/29/2017	12	9	75%

<tbody>
</tbody><colgroup><col><col span="2"><col><col></colgroup>

 

[AFPathfinder]

You would split up the date logic between the two dates it's validating against. I don't know what your data sheet looks like so I can't write the formula for you, but you'll end up with 3 criteria ranges and 3 criteria: >=start date, < end date, and X or Y. Example of formula in cell E3:

=AVERAGEIFS([averagethisrange],[COLOR=#008000][criteriarange1],">="&A3[/COLOR],[COLOR=#0000ff][criteriarange2],"<"&A4[/COLOR],[COLOR=#b22222][criteriarange3],$A$1[/COLOR])

Then you just use the fill handle to copy the formula down to the rest of the X table rows.