Weighted average with SUMIFS

[groovadelic]

Hello - totally stumped and would appreciate if someone knows how to do a weighted average when working with SUMIFS.

Here is my Data table

A​	B​	C​	D​	E​	F​	G​	H​
Platform 1	Platform 2	Date	Action 1 Total	Action 1 Median Turnaround	Total Action 2	Inactive Action 2	Action 2 Turnaround
A	X	4/3/23	9	1	8	1	73.1
A	X	4/10/23	10	1	9	1	8.2
A	Z	4/17/23	12	1	10	2	30.9
A	Y	4/24/23	13	1	12	1	37.5
A	Z	5/1/23	8	0	8	0	29.3
A	X	5/8/23	7	0.94	6	1	30.9
A	Z	5/15/23	14	1	13	1	41.2
A	X	5/22/23	10	1	9	1	19.1
A	Z	5/29/23	9	1.87	8	1	51.9
A	Y	6/5/23	500	4.81	496	4	23.4
A	Z	6/12/23	157	22.96	156	1	20.2

Here is what I'm trying to captured in monthly tabs - this is April.

A​	B​	C​	D​	E​	F​
April	A
	Action 1 Total	Action 1 Average Turnaround	Total Action 2	Total Action 2 Inactive	Action 2 Average Turnaround
X	19	1	17	2	41
Y	13	1	12	1	38
Z	12	1	10	2	31

Formula for Action 1 Total: =SUMIFS(Data!D:D,Data!A:A,B$1,Data!B:B,A4,Data!C:C,">="&DATE(2023,4,1),Data!C:C,"<"&DATE(2023,4,31))
Action 2 Total is similar: =SUMIFS(Data!F:F,Data!A:A,B$1,Data!B:B,A4,Data!C:C,">="&DATE(2023,4,1),Data!C:C,"<"&DATE(2023,4,31))
Same for Action 2 Inactive Total.

I was calculating average action 1 turnaround and action 2 turnaround by taking the Total ÷ a COUNTIFS

What I really want is the weighted average of Turnaround Action 1 (column E) based on the total in column D and weighted average "Turnaround action 2" (column H) based on column F.

Can anyone help?

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F	G	H
1	Platform 1	Platform 2	Date	Action 1 Total	Action 1 Median Turnaround	Total Action 2	Inactive Action 2	Action 2 Turnaround
2	A	X	4/3/2023	9	1	8	1	73.1
3	A	X	4/10/2023	10	1	9	1	8.2
4	A	Z	4/17/2023	12	1	10	2	30.9
5	A	Y	4/24/2023	13	1	12	1	37.5
6	A	Z	5/1/2023	8	0	8	0	29.3
7	A	X	5/8/2023	7	0.94	6	1	30.9
8	A	Z	5/15/2023	14	1	13	1	41.2
9	A	X	5/22/2023	10	1	9	1	19.1
10	A	Z	5/29/2023	9	1.87	8	1	51.9
11	A	Y	6/5/2023	500	4.81	496	4	23.4
12	A	Z	6/12/2023	157	22.96	156	1	20.2
13
14
15	April	A
16		Action 1 Total	Action 1 Average Turnaround	Total Action 2	Total Action 2 Inactive	Action 2 Average Turnaround
17	X	19	1.00	17	2	38.74
18	Y	13	1.00	12	1	37.50
19	Z	12	1.00	10	2	30.90
Sheet1

Cell Formulas
Range	Formula
C17:C19	C17	=SUMPRODUCT(--(MONTH($C$2:$C$12)=4),--($A$2:$A$12=$B$15),--($B$2:$B$12=$A17),$D$2:$D$12,$E$2:$E$12)/$B17
F17:F19	F17	=SUMPRODUCT(--(MONTH($C$2:$C$12)=4),--($A$2:$A$12=$B$15),--($B$2:$B$12=$A17),$F$2:$F$12,$H$2:$H$12)/$D17

 

[groovadelic]

Wow - thank you! It works!

 

[AhoyNC]

You're welcome. Thanks for the feedback.