Why does this Sumifs expression get an error?

[JenniferMurphy]

I need to calculate the average ratio of a set of numbers. This minisheet is a simple example.

In P11, I want to calculate the average ratio of the values in L/K for each value in J. The expression in O11 works, but the one in P11 does not. Can anyone tell me why? And what do I need to do to make it work. The values in J11:L16 are sorted on J, but I need the expression to work no matter how they are sorted.

Average Ratings.xlsx
	J	K	L	M	N	O	P
11	a	2	4		a	2	=sumifs(L11:L16/K11:K16,J11:J16,N11)
12	a	3	6		b	3
13	a	4	8
14	b	2	6
15	b	3	9
16	b	4	12
Test

Cell Formulas
Range	Formula
O11	O11	=SUM(L11:L13/K11:K13)/3
O12	O12	=SUM(L14:L16/K14:K16)/3

Thanks

 

[kevin9999]

Does this work for you?

clean.xlsm
	J	K	L	M	N	O	P
11	a	2	4		a	2	2
12	a	3	6		b	3	3
13	a	4	8
14	b	2	6
15	b	3	9
16	b	4	12
Sheet2

Cell Formulas
Range	Formula
O11	O11	=SUM(L11:L13/K11:K13)/3
P11:P12	P11	=SUMIF(J11:J16,N11,L11:L16)/SUMIF(J11:J16,N11,K11:K16)
O12	O12	=SUM(L14:L16/K14:K16)/3

 

[JenniferMurphy]

Does this work for you?

That only works if the ratios are all the same. I made them that way in the example above so I could reasily calculate it manually.

It does not work in this case. I added columns Q & R to help illustrate. Q is the ratios, which in the example above were all 2 for "a" and 3 for "b". R takes the average of the corresponding values (ratios) in M. I'd like a formula that does that without the intermediate values in Q.

Average Ratings.xlsx
	J	K	L	M	N	O	P	Q	R
11	a	2	6		a	2.2500	2.1111	3.000	2.2500
12	a	3	6		b	2.8889	3.0000	2.000	2.8889
13	a	4	7					1.750
14	b	2	5					2.500
15	b	3	8					2.667
16	b	4	14					3.500
Test

Cell Formulas
Range	Formula
O11	O11	=SUM(L11:L13/K11:K13)/3
P11:P12	P11	=SUMIF(J11:J16,N11,L11:L16)/SUMIF(J11:J16,N11,K11:K16)
O12	O12	=SUM(L14:L16/K14:K16)/3
R11	R11	=SUMIFS(Q11:Q16,J11:J16,N11)/COUNTIFS(J11:J16,N11)
R12	R12	=SUMIFS(Q11:Q16,J11:J16,N12)/COUNTIFS(J11:J16,N12)
Q11:Q16	Q11	=L11/K11

Is that clearer?

 

[Alex Blakenburg]

Typically an average of an average is mathematically invalid. This will give you the same result as above but it is longer so might not be worth it.

=LET(fltr,FILTER((L11:L16/K11:K16),J11:J16=N11),SUM(fltr)/COUNT(fltr))

 

[JenniferMurphy]

Typically an average of an average is mathematically invalid.

I don't think this is an average of an average.

This is what I am trying to do. I have a table of the amount of baking time it took to cook three different types of potatoes at three different oven temperatures. I wanted to see if I could calculate an average bake time at each oven temperature. To do that, I chose an average potato weight of 350g. Here are the tables.

The first one is the raw data. I've sorted it by Temp and then by Weight to make it easier to read.

The second one calculates the average ratings for each type of potato. That is working.

The last one is trying to calculate the average cook times for each of the three temperatures. To make it work, I had to add column J to the raw data table.

My question is how I can calculate the values in column Q without the values in column J?

Average Ratings.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	Date	Item	Rating	Weight	Temp	Bake Time	Time /350g		Item	Rating	Count		Temp	Time /350g
5	9/21/22	Yam	92	296g	300°	2.75	3.25		Yam	93.20	5		300°	3.27
6	9/18/22	Russet	75	312g	300°	2.96	3.32		Sweet	86.00	5		350°	2.71
7	10/06/22	Sweet	85	343g	300°	3.24	3.31		Russet	75.00	4		400°	2.19
8	10/14/22	Russet	70	396g	300°	3.60	3.18
9	9/22/22	Russet	80	325g	350°	2.58	2.78
10	9/25/22	Yam	90	347g	350°	2.71	2.73
11	9/28/22	Sweet	85	378g	350°	2.88	2.67
12	10/03/22	Yam	95	413g	350°	3.28	2.78
13	10/09/22	Sweet	85	423g	350°	3.15	2.61
14	9/30/22	Yam	94	285g	400°	1.83	2.25
15	9/23/22	Russet	75	293g	400°	1.87	2.23
16	10/12/22	Sweet	85	338g	400°	2.10	2.17
17	9/29/22	Sweet	90	389g	400°	2.33	2.10
18	10/08/22	Yam	95	418g	400°	2.61	2.19
19	Averages		85.4	354g
Multiple Properties

Cell Formulas
Range	Formula
M5:M7	M5	=IF([@Count]>0,SUMIFS(TblRtgs3[Rating],TblRtgs3[Item],[@Item])/[@Count],"--")
N5:N7	N5	=COUNTIFS(TblRtgs3[Item],[@Item])
Q5:Q7	Q5	=AVERAGEIFS(TblRtgs3[Time /350g],TblRtgs3[Temp],[@Temp])
J5:J18	J5	=[@[Bake Time]]/[@Weight]*350
F19	F19	=SUBTOTAL(101,[Rating])
G19	G19	=SUBTOTAL(101,[Weight])

 

[Alex Blakenburg]

This will replicate your results without using Column J

=LET(fltr,FILTER((TblRtgs3[Bake
Time]/TblRtgs3[Weight]),TblRtgs3[Temp]=[@Temp]),SUM(fltr)/COUNT(fltr)*350)

 

[JenniferMurphy]

This will replicate your results without using Column J

=LET(fltr,FILTER((TblRtgs3[Bake
Time]/TblRtgs3[Weight]),TblRtgs3[Temp]=[@Temp]),SUM(fltr)/COUNT(fltr)*350)

That is incredible. I will have to study that one. I could never have come up with that myself. Thank you very much.

Here it is in action:

Average Ratings.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4	Date	Item	Rating	Weight	Temp	Bake Time	Time /350g		Item	Rating	Count		Temp	Time /350g	With Col J
5	9/21/22	Yam	92	296g	300°	2.75	3.25		Yam	93.20	5		300°	3.27	3.27
6	9/18/22	Russet	75	312g	300°	2.96	3.32		Sweet	86.00	5		350°	2.71	2.71
7	10/06/22	Sweet	85	343g	300°	3.24	3.31		Russet	75.00	4		400°	2.19	2.19
8	10/14/22	Russet	70	396g	300°	3.60	3.18
9	9/22/22	Russet	80	325g	350°	2.58	2.78
10	9/25/22	Yam	90	347g	350°	2.71	2.73
11	9/28/22	Sweet	85	378g	350°	2.88	2.67
12	10/03/22	Yam	95	413g	350°	3.28	2.78
13	10/09/22	Sweet	85	423g	350°	3.15	2.61
14	9/30/22	Yam	94	285g	400°	1.83	2.25
15	9/23/22	Russet	75	293g	400°	1.87	2.23
16	10/12/22	Sweet	85	338g	400°	2.10	2.17
17	9/29/22	Sweet	90	389g	400°	2.33	2.10
18	10/08/22	Yam	95	418g	400°	2.61	2.19
19	Averages		85.4	354g
Multiple Properties

Cell Formulas
Range	Formula
M5:M7	M5	=IF([@Count]>0,SUMIFS(TblRtgs3[Rating],TblRtgs3[Item],[@Item])/[@Count],"--")
N5:N7	N5	=COUNTIFS(TblRtgs3[Item],[@Item])
Q5:Q7	Q5	=LET(fltr,FILTER((TblRtgs3[Bake Time]/TblRtgs3[Weight]),TblRtgs3[Temp]=[@Temp]),SUM(fltr)/COUNT(fltr)*350)
R5:R7	R5	=AVERAGEIFS(TblRtgs3[Time /350g],TblRtgs3[Temp],[@Temp])
J5:J18	J5	=[@[Bake Time]]/[@Weight]*350
F19	F19	=SUBTOTAL(101,[Rating])
G19	G19	=SUBTOTAL(101,[Weight])

 

[Alex Blakenburg]

No problem. Let (groan) me know if you need me to talk you through it.
The "Let" statement just allows you to use variable names like you do in VBA. So in this case the Filter function is used twice in the final calculation but it is only calculated once and assigned to the variable fltr.

 

[JenniferMurphy]

No problem. Let (groan) me know if you need me to talk you through it.
The "Let" statement just allows you to use variable names like you do in VBA. So in this case the Filter function is used twice in the final calculation but it is only calculated once and assigned to the variable fltr.

Interesting. That looks like it could be quite handy. I'll add that to my (way too long) Excel To Do list.

 

[JenniferMurphy]

No problem. Let (groan) me know if you need me to talk you through it.

Perhaps you can explain why the Sum function will accept an expression as an argument (Q11:Q12), but neither SumIf nor SumIfs will (P11:P12 & R11:R12), which I have commented out because they get an error.

Average Ratings.xlsx
	J	K	L	M	N	O	P	Q	R
11	a	4	7		a	2.2500	=sumifs(L11:L16/K11:K16,J11:J16,N11)	2.2500	=sumif(J11:J16,N11,L11:L16/K11:K16)
12	a	2	6		b	2.8889	=sumifs(L11:L16/K11:K16,J11:J16,N12)	2.8889	=sumif(J11:J16,N12,L11:L16/K11:K16)
13	a	3	6
14	b	3	8
15	b	4	14
16	b	2	5
Test

Cell Formulas
Range	Formula
O11	O11	=SUMIFS(T11:T16,J11:J16,N11)/COUNTIFS(J11:J16,N11)
O12	O12	=SUMIFS(T11:T16,J11:J16,N12)/COUNTIFS(J11:J16,N12)
Q11	Q11	=SUM(L11:L13/K11:K13)/3
Q12	Q12	=SUM(L14:L16/K14:K16)/3