Average a range of values if the date associated with it falls within a date range

excel-formula sumifs
Ryan Chase picture Ryan Chase · Apr 29, 2015 · Viewed 36.8k times · Source

I'm trying to average a range of values if its associated date falls between two specified dates. The following function works:

AVERAGEIFS($1:$1,$2:$2,">=1/1/2014",$2:$2,"<=1/2/2014")

...in this case the values that you want to average are in row 1 and the dates that are associated with it are in row 2.

However, in this case I'm explicitly stating the date range in my formula (">=1/1/2014" and "<=1/2/2014"). Is there any way to create a similar formula that allows me to reference date cells to determine my date range instead of having to explicitly state the dates in the formula itself???

Answer

Marcel picture Marcel · Apr 29, 2015

you can use the following, by putting the start date and the end date in another cell and refer to them in your formula:

=AVERAGEIFS($1:$1,$2:$2,">="&$B$4,$2:$2,"<="&$B$5)

with the following example:
enter image description here

Related questions

SUMPRODUCT vs SUMIFS

I'm trying to make an in-company guide to Excel (we're a small non-profit and sorely need some sort of baseline guide). However, I've gotten stuck trying to explain the differences between SUMPRODUCT and SUMIFS. My understanding is that SUMPRODUCT was …

Read More
Selecting a Specific Column of a Named Range for the SUMIF Function

I am trying to create a SUMIF function that dynamically adds up values in a specific column of a named range in my Excel sheet. It is very easy to do this when there is no named range : The formula …

Read More
SUMIFS with date and name criteria... month and year only

I am trying to get a SUMIFS formula to check a column of dates and sum only the values that correspond to the matching year and month of the criterion date. I would also like this SUMIFS to include a …

Read More