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

 

[jasonb75]

You need to do SUM/COUNT, the average functions don't work with 'OR' criteria types.

For example

=SUM(SUMIFS(Call_Data!Q:Q,Call_Data!F:F,{"Description 1","Description 2"}))/SUM(COUNTIFS(Call_Data!F:F,{"Description 1","Description 2"}))

 

[Peter_SSs]

What version of Excel are you using? I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[gmazza76]

I am using Office 365 Pro Plus

 

[gmazza76]

I have updated my profile.

@jasonb75 how would I put the date restrictions into the formula as I will be doing a couple of ranges as well as the previous day

thanks

 

[Peter_SSs]

Then if you have the FILTER function, try a structure like this. I would avoid whole column references if possible.

20 04 01.xlsm
	F											Q	R	S
1
2	Description 1											1		3.33333
3	Description 3											2
4	Description 3											3
5	Description 2											4
6	Description 1											5
7	Description 3											6
8
Average

Cell Formulas
Range	Formula
S2	S2	=AVERAGE(FILTER(Q2:Q7,(F2:F7="Description 1")+(F2:F7="Description 2")))

 

[jasonb75]

This should do it

=SUM(SUMIFS(Call_Data!Q:Q,Call_Data!F:F,Call_Data!C:C,Calcs!$B$6-1,{"Description 1","Description 2"}))/SUM(COUNTIFS(Call_Data!C:C,Calcs!$B$6-1,Call_Data!F:F,{"Description 1","Description 2"}))

 

[Peter_SSs]

how would I put the date restrictions into the formula

Like this for me. The formula is averaging the two coloured cells.

BTW, thanks for updating your profile.

20 04 01.xlsm
	B	C	D	E	F	G									P	Q	R	S
1
2		31/03/2020			Description 1											1		2.5
3		28/03/2020			Description 3											2
4		28/03/2020			Description 3											3
5		31/03/2020			Description 2											4
6	01-Apr-20	28/03/2020			Description 1											5
7		28/03/2020			Description 3											6
Average

Cell Formulas
Range	Formula
S2	S2	=AVERAGE(FILTER(Q2:Q7,((F2:F7="Description 1")+(F2:F7="Description 2"))*(C2:C7=B6-1)))
B6	B6	=TODAY()

 

[gmazza76]

thank you both,
I will try both of these and see which works best for the current issue

 

[jasonb75]

I would say that Peter's suggestion would be the better of the 2, but remember that it only works with office 365, if you have to send the workbook to another user on an older version of excel then they will see #NAME? instead of the results. The method that I suggested will work with any versions back to 2007.