Averageifs Question

[gmazza76]

Good afternoon,

I am trying to use the"averageifs" function to average a column based on the description and date.

I have been able to use the "averageifs" to match "1 description" and a date criteria (B6-1 yesterdays date) Formula 1, but I need to do this to include up to 3 descriptions that fall under 1 group.

I have tried the following as an array "Formula 2" which works but I am unsure how I can add the date specifications in.
I have tried doing 2 averageifs adding them together and dividing by 2 but this doesn't work

Formula 1
=AVERAGEIFS(Call_Data!Q:Q,Call_Data!F:F,"Description",Call_Data!C:C,Calcs!$B$6-1)
[code/]
[code]
Formula 2
=AVERAGE(IF(ISNUMBER(MATCH(Call_Data!F:F, {"Description 1","Description 2"},0)),Call_Data!Q:Q))
[code/]

thanks in advance

 

[Peter_SSs]

I think for a longer list of descriptions, I would be inclined to list them in the sheet rather than the formula,

=AVERAGE(IF(Call_Data!C2:C7=Calcs!B6-1,IF(F2:F7=TRANSPOSE(List!A2:A10),Call_Data!Q2:Q7)))

where List!A2:A10 holds the list of descriptions.

Have to be careful about blanks in List!A2:A10 and/or Call_Data! column F with that structure??

Single sheet demo here where this averages Q2, Q3 and Q5 (6 times)

20 04 01.xlsm
	A	B	C			F											Q
1
2	Description 1		31/03/2020			Description 1											1
3	Description 2		31/03/2020			Description 3											2
4	Description 3		28/03/2020			Description 3											3
5			31/03/2020														4
6		1/04/2020	28/03/2020			Description 1											5
7			28/03/2020			Description 3											6
8
9		3.375
Sample

Cell Formulas
Range	Formula
B6	B6	=TODAY()
B9	B9	=AVERAGE(IF(C2:C7=B6-1,IF(F2:F7=TRANSPOSE(A2:A10),Q2:Q7)))