Using SUMPRODUCT instead of AVERAGEIFS due to Average Range <> Criteria Range size

[Hawk11ns]

I have 10 years worth of 24-hour data in range G3:P35162 and need to average only the data that meets the criteria of being equal to "Jan" in range A3:P35162 and "1" in range D3:D35162. I cannot perform this function using AVERAGEIFS since the ranges are not the same size, spanning multiple columns. I do recall coming across a solution, once before, using SUMPRODUCT - but for the life of me I cannot find it and every iteration I try does not work. Any guidance is much appreciated. Thanks!

 

[AhoyNC]

If your version of Excel 365 has the FILTER function try the formula in L2, if not maybe the SUMPRODUCT formula in L5 in the example below. Change ranges to match your data. Also, I assumed the months in your column A are text and the numbers in column D are numeric. If they are actual dates formatted as mmm, then you will need to change the formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Month	Peak	Cal Day	HE	IE	DST	2011	2012	2013	2014		Average
2	Jan			1			15.25	36.77	40.23	44.39		29.6525
3	Jan			1			34.14	34.43	42.75	16.24
4	Jan			2			43.42	44.2	35.85	44.36
5	Jan			3			38.97	42.28	25.72	44.26		29.6525
6	Jan			2			27.23	39.98	34.29	35.26
7	Jan			1			30.18	12.24	18.92	26.95
8	Jan			1			20.82	38.51	26.66	35.96
9	Feb			1			24.72	38.37	13.12	16.46
10	Feb			1			22.61	18	34.55	36.04
11	Feb			2			21.37	36.56	41.37	43.13
12	Feb			2			19.94	39.77	42.9	20.2
13	Feb			2			29.3	23.9	30.3	24.64
14	Feb			3			30.73	12.89	12.45	14.02
15	Feb			4			17.17	35.91	27.24	43.87
16	Feb			5			34.27	30.18	41.74	41.04
Sheet1

Cell Formulas
Range	Formula
L2	L2	=AVERAGE(FILTER(G2:J16,($A$2:$A$16="Jan")*($D$2:$D$16=1)))
L5	L5	=SUMPRODUCT(($A$2:$A$16="Jan")*($D$2:$D$16=1)*(G2:J16))/SUMPRODUCT(($A$2:$A$16="Jan")*($D$2:$D$16=1)*(G2:J16/G2:J16))

 

[DRSteele]

Without FILTER, this will work.

MrExcel posts18.xlsx
	A	B	C	D	E	F	G	H	I
1	Month			HE			2011	2012	2013
2	Jan			1			20	20	17
3	Jan			1			17	13	24
4	Jan			1			22	23	25
5	Jan			1			17	10	18
6	Jan			3			21	22	17
7	Feb			2			12	20	13
8	Feb			4			16	23	12
9	Feb			1			21	17	16
10
11	Criteria
12	Month	HE		sum	count	average	average (single-cell)
13	Jan	1		226	12	18.83333	18.83333
Sheet48

Cell Formulas
Range	Formula
D13	D13	=SUMPRODUCT((A2:A9=A13)*(D2:D9=B13)*G2:I9)
E13	E13	=COUNT(1/((A2:A9=A13)*(D2:D9=B13)*G2:I9))
F13	F13	=D13/E13
G13	G13	=SUMPRODUCT((A2:A9=A13)*(D2:D9=B13)*G2:I9)/COUNT(1/((A2:A9=A13)*(D2:D9=B13)*G2:I9))

 

[Hawk11ns]

If your version of Excel 365 has the FILTER function try the formula in L2, if not maybe the SUMPRODUCT formula in L5 in the example below. Change ranges to match your data. Also, I assumed the months in your column A are text and the numbers in column D are numeric. If they are actual dates formatted as mmm, then you will need to change the formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Month	Peak	Cal Day	HE	IE	DST	2011	2012	2013	2014		Average
2	Jan			1			15.25	36.77	40.23	44.39		29.6525
3	Jan			1			34.14	34.43	42.75	16.24
4	Jan			2			43.42	44.2	35.85	44.36
5	Jan			3			38.97	42.28	25.72	44.26		29.6525
6	Jan			2			27.23	39.98	34.29	35.26
7	Jan			1			30.18	12.24	18.92	26.95
8	Jan			1			20.82	38.51	26.66	35.96
9	Feb			1			24.72	38.37	13.12	16.46
10	Feb			1			22.61	18	34.55	36.04
11	Feb			2			21.37	36.56	41.37	43.13
12	Feb			2			19.94	39.77	42.9	20.2
13	Feb			2			29.3	23.9	30.3	24.64
14	Feb			3			30.73	12.89	12.45	14.02
15	Feb			4			17.17	35.91	27.24	43.87
16	Feb			5			34.27	30.18	41.74	41.04
Sheet1

Cell Formulas
Range	Formula
L2	L2	=AVERAGE(FILTER(G2:J16,($A$2:$A$16="Jan")*($D$2:$D$16=1)))
L5	L5	=SUMPRODUCT(($A$2:$A$16="Jan")*($D$2:$D$16=1)*(G2:J16))/SUMPRODUCT(($A$2:$A$16="Jan")*($D$2:$D$16=1)*(G2:J16/G2:J16))

Thanks for replying - I was completely unaware of the Office 365 filter function, and now I am totally in love with it! hahaha

Thanks again!

 

[AhoyNC]

You're welcome. Thanks for the feedback.