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

 

[gmazza76]

Sorry, both formulas give an error
@jasonb75 - it doesnt think its a formula as an "' or() is missing
@Peter_SSs - function isnt valid and this shows on FILTER

thanks

 

[jasonb75]

Oops, I added the date criteria in the wrong place, can't see any more errors

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

 

[gmazza76]

thanks for the amendment @jasonb75

 

[Peter_SSs]

Edit: Ah, I see you picked this up,

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"}))

@jasonb75
I think you have the order of your arguments confused. Should be this I think?

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

@gmazza76
If you do have an older version, you can also use this, which must be confirmed with Ctrl+Shift+Enter not just Enter
(Adjust ranges of course)

=AVERAGE(IF(Call_Data!C2:C7=Calcs!B6-1,IF(OR(Call_Data!F2:F7={"Description 1","Description 2"}),Call_Data!Q2:Q7)))

 

[Peter_SSs]

@Peter_SSs - function isnt valid and this shows on FILTER

OK, I did say IF you have the FILTER function. Some people with Excel 365 have it and others don't - yet.
Try my alternative just posted instead. If using 365 you probably do NOT need the Ctrl+Shift+Enter

 

[jasonb75]

=AVERAGE(IF(Call_Data!C2:C7=Calcs!B6-1,IF(OR(Call_Data!F2:F7={"Description 1","Description 2"}),Call_Data!Q2:Q7)))

Are you sure about that, Peter? I thought it would be more like

=AVERAGE(IF(Call_Data!C2:C7=Calcs!B6-1,IF((Call_Data!F2:F7="Description 1")+(Call_Data!F2:F7="Description 2"),Call_Data!Q2:Q7)))

 

[Peter_SSs]

Are you sure about that, Peter?

Here are my sheets and calc.

20 04 01.xlsm
	B
5
6	1/04/2020
Calcs

Cell Formulas
Range	Formula
B6	B6	=TODAY()

20 04 01.xlsm
	C	D	E	F	G									P	Q
2	31/03/2020			Description 1											1
3	28/03/2020			Description 3											2
4	28/03/2020			Description 3											3
5	31/03/2020			Description 2											4
6	28/03/2020			Description 1											5
7	28/03/2020			Description 3											6
Call_Data

20 04 01.xlsm
	E
1	Average
2	2.5
Frmla

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(IF(Call_Data!C2:C7=Calcs!B6-1,IF(OR(Call_Data!F2:F7={"Description 1","Description 2"}),Call_Data!Q2:Q7)))

 

[jasonb75]

Change the date in C3 to 31/3/20 (or today -1) then check the result.

The results are only correct because there are not rows where the date meets the criteria but the description doesn't'

 

[Peter_SSs]

Change the date in C3 to 31/3/20 (or today -1) then check the result.

The results are only correct because there are not rows where the date meets the criteria but the description doesn't'

Oops, good point. Thanks for the catch & a correct alternative.

If there are more than 2 descriptions to match then this structure would become progressively more appealing

=AVERAGE(IF(Call_Data!C2:C7=Calcs!B6-1,IF(ISNUMBER(SEARCH("|"&Call_Data!F2:F7&"|","|Description 1|Description 2|Description 3|")),Call_Data!Q2:Q7)))

 

[jasonb75]

The same problem with OR caught me out a couple of times, Peter. If it hadn't then I probably wouldn't have noticed it.
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.