Pivot Table Conditional Weighted Average

[GoldOnion]

Hello, I am looking for some advice on how to find the weighted average (using m2 & $/m2) of a set of values based on certain criteria within a pivot table.

I have a pivot table similar to the table below. Not shown is the fact that I am filtering these values by month. The reason I am doing this is beacuse I would like to create a table that I can sort by timeframe that displays the "$/m2" for each "actual category" across thousands of "Tags". I am able to count the number of distinct categories using this formula...

SUMPRODUCT(--(ISNUMBER(SEARCH("1",E3:E11))),SUBTOTAL(3,OFFSET(E3,ROW(E3:E11)-ROW(E3),0)))

and I am able to calculate the weighted average using this formula...

SUMPRODUCT(SUBTOTAL(3,OFFSET(C3:C11,ROW(C3:C11)-ROW(C3),0,1)),C3:C11,B3:B11)/SUBTOTAL(9,B3:B11)

However, I am struggling to combine the two formulas to get it to behave like I want (which is for the "category 1" to only contain C6 & C7 and average at $2.67, and "category 2" to contain C1,C2,C3,C4,C5,C8 and to average $2.08)
I am not super proficient with Excel and tried to combine these two formulas to no avail with the logic that it would search for tags in category 1 then run the weighted average formula on them. I don't think I am fully understanding what is happening inside the formula, so I can not identify what the problem is.SUMPRODUCT(--(ISNUMBER(SEARCH("1",E3:E11))),SUBTOTAL(3,OFFSET(C3:C11,ROW(C3:C11)-ROW(C3),0,1)),C3:C11,B3:B11)/SUBTOTAL(9,B3:B11)))

Any help would be appreciated. Thanks in advance.


a b c d e

Tag #	m2	$/m2	Proj. Category	Actual Category
C1	717.97	1.5	1	2
C2	798.18	2.17	1	2
C3	476.74	2.27	1	2
C4	720.38	2.26	1	2
C5	804.88	2.17	1	2
C6	791.84	3.5	1	1
C7	1018.24	2.03	1	1
C8	1012.6	2.12	1	2
C9	665.93	2.22	1	4
	Category 1	2.24		2
	Category 2	2.24		5

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col></colgroup>