Weighted average while omitting cells from an array

[UndwaterExcelWeaver]

I understand how to do a basic weighted average using sumproduct but I cannot figure out how to do this when there are rows within the array that I do not want to be included. I have tried adding and subtracting multiple sumproducts and sums and even tried the vstack formula but nothing seems to return the accurate results. Sorry, I couldn't get the xl2bb to capture this. I need to calculate the weighted average in cells D18:G18. I need it to use the values in rows 12, 13, and 16, but omit rows 14 and 15. How do I accomplish this?

View attachment 91947

Nancy Cooker Water Produced	20814​	22.67	0.50	20.38	(2.29)
String Make Cook Water Produced	38997​	0.42	0.00	0.00	-0.42
Sweet Cream from Milk Reception					0.00
Site Total From EOD Totalizer	156,375	30.20%	0.50%	31.96%	1.76%
Total Mozz produced using site total	96564	11.60	0.08	12.31	0.71
	-	30.00%	0.50%	31.93%	1.93%
Total Inventory (Using formula)	156,375

 

[Oaktree]

You'd basically just expand the SUMPRODUCT you're already using to exclude the criterion.

For example, if you currently have:

 =SUMPRODUCT(C12:C16,D12:D16)/SUM(C12:C16)

You could expand that to be something like:

 =SUMPRODUCT(C12:C16,D12:D16,--(B12:B16<>"String Make Cook Water Produced"))/SUMPRODUCT(C12:C16,--(B12:B16<>"String Make Cook Water Produced"))

Your actual criteria will no doubt differ, but the general construct is there.