# Finding average between dates that reference a cell for names

1. ## Finding average between dates that reference a cell for names

I have a sheet "data!" set going back 5 years on rows 2-5000.
Names in column Q and a time in column AK.

I have second sheet "2018 Analysis" with names in cells K7:K10 that I need to reference against the first sheet.

I have dates Jan 1st 2018 in cell A1 and Dec 31st 2018 in cell B2.

I need cells R7:R10 to show me the average time on a column AK in "data!" in the between the dates in cells A1 & A2 on "2018 Analysis" for the name in cells K7:K10.

I have managed to get this to work when calculating a SUM, but I need an AVERAGEIF.

The formula in different cells is:
=SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3))

This works and calculates the sum between the cell ranges, references the name (K7) and also only on dates required between cells A2 & A3

Hope someone can help!  Register To Reply

2. ## Re: Finding average between dates that reference a cell for names

Hi

the following formula should work as a COUNTIF

=SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3))

=SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3)*AK2:AK1832)

you should get the sum of numbers (times) in AK2:AK1832 under same conditions

To get the average

=SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3)*AK2:AK1832)/SUMPRODUCT((Data!Q2:Q1832=K7)*(Data!G2:G1832>=A2)*(Data!G2:G1832<=A3))

Or to be confirmed with control+shift+enter

=AVERAGE(IF(data!Q2:Q1832=K7,IF(data!G2:G1832>=A2,IF(data!G2:G1832<=A3,AK2:AK1832))))

Regards  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 