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!

[TABLE="width: 342"]
<tbody>[TR]
[TD="colspan: 5"]X[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Units Needed[/TD]
[TD]Units Built[/TD]
[TD]Percentage Built[/TD]
[TD]Avg. Days to Build[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]83%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]70%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2017[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]64%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2017[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]85%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2017[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]90%[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col></colgroup>[/TABLE]

[TABLE="width: 340"]
<tbody>[TR]
[TD="colspan: 5"]Y[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Units Needed[/TD]
[TD]Units Built[/TD]
[TD]Percentage Built[/TD]
[TD]Avg. Days to Build[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]90%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]54%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2017[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]64%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2017[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]110%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2017[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]75%[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col></colgroup>[/TABLE]

 

[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.