Averageifs with Or Condition

[andybason]

Hi,

I have put together this formula and would like to add an "or" condition:

=AVERAGEIFS(E1:E27,A1:A27,N1,G1:G27,120)

In addition to averaging these results I would like to include the values in E where the adjacent cell in F is empty and the adjacent value in G is less than 120.

Is this possible?

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Adelaide	Adelaide Strikers	field	Heat won by 10 runs	206	5	120	196	6			174.5833333		Adelaide
2	Adelaide	Sydney Sixers	field	Strikers won by 48 runs	152	9	120	104
3	Adelaide	Adelaide Strikers	field	Strikers won by 5 wickets (with 10 balls remaining)	161	8	120	164	5
4	Adelaide	Adelaide Strikers	field	Melb Reneg won by 6 runs	171	9	120	165	8
5	Adelaide	Sri Lanka	field	Australia won by 41 runs	187	6	120	146
6	Adelaide	Adelaide Strikers	bat	Strikers won by 53 runs	163	6	120	110
7	Adelaide	Adelaide Strikers	bat	Strikers won by 56 runs	147	7	120	91
8	Adelaide	Melbourne Stars	bat	Strikers won by 8 wickets (with 8 balls remaining)	151	6	120	152	2
9	Adelaide	Adelaide Strikers	bat	Strikers won by 11 runs	187	4	120	176	4
10	Adelaide	Adelaide Strikers	bat	Strikers won by 1 run	178	5	120	177	4
11	Adelaide	Adelaide Strikers	bat	Strikers won by 25 runs	202	2	120	177	5
12	Adelaide	Melbourne Renegades	field	Melb Reneg won by 5 wickets (with 5 balls remaining)	174	5	120	176	5
13	Adelaide	Adelaide Strikers	bat	Strikers won by 20 runs	175	4	120	155	6
14	Adelaide	Adelaide Strikers	bat	Syd Sixers won by 6 wickets (with 10 balls remaining)	150	5	120	152	4
15	Adelaide	Melbourne Stars	field	Strikers won by 41 runs	178	5	120	137
16	Adelaide	Adelaide Strikers	bat	Hurricanes won by 10 wickets (with 19 balls remaining)	154	5	120	158	0
17	Adelaide	Brisbane Heat	field	Heat won by 6 wickets (with 8 balls remaining)	176	8	120	180	4
18	Adelaide	Perth Scorchers	bat	Strikers won by 5 wickets (with 4 balls remaining)	174	6	120	175	5
19	Adelaide	Sri Lanka	field	Australia won by 134 runs	233	2	120	99	9
20	Adelaide	Perth Scorchers	field	Strikers won by 15 runs (D/L method)	198	4	108	183	7
21	Adelaide	Sydney Thunder	bat	Syd Thunder won by 3 runs	168	5	120	165	9
22	Adelaide	Sydney Sixers	field	Syd Sixers won by 2 wickets (with 8 balls remaining)	135		118	137	8
23	Adelaide	Adelaide Strikers	bat	Strikers won by 63 runs	173	6	120	110
24	Adelaide	Brisbane Heat	bat	Strikers won by 10 wickets (with 55 balls remaining)	100		102	104	0
25	Adelaide	Melbourne Stars	field	Strikers won by 11 runs	162	4	120	151	8
26	Adelaide	Hobart Hurricanes	bat	Hurricanes won by 10 runs	217	1	120	207	8
27	Adelaide	Adelaide Strikers	field	Syd Thunder won by 8 runs	151	7	120	143	9
Sheet1

Cell Formulas
Range	Formula
L1	L1	=AVERAGEIFS(E1:E27,A1:A27,N1,G1:G27,120)

 

[jasonb75]

Not with averagifs, you need to do it the long way

=(SUMIFS(E1:E27,A1:A27,N1,G1:G27,120)+SUMIFS(E1:E27,F1:F27,"<>",G1:G27,"<120"))/(COUNTIFS(A1:A27,N1,G1:G27,120)+COUNTIFS(F1:F27,"<>",G1:G27,"<120"))

 

[andybason]

Thank you