Weighted Average Sumproduct Multiple Criteria with Data in One Column

Hi, I'm looking for a formula/approach to perform a weighted average across multiple criteria with the data in the same column.

This has been bugging me for some time and I know I can count on the EF contributors for direction.

My data consists of two columns: ColA is the criteria, ColB contains the values.

ColA	ColB
AG	1
AG-TERM	2
AG-VALUE	3
ZP	11
ZP-TERM	12
ZP-VALUE	13
ZP	21
ZP-TERM	22
ZP-VALUE	23

GOAL: use a dynamic formula that will calc an accurate wght. avg based on the multiple criteria selections from ColA.

Example 1: Calc the Wght. Avg. of "ZP-TERM" (returning value should = 18.5625). Formula: (11*12+21*22)/SUM(11,21)
Example 2: Calc the Wght. Avg. of "ZP-VALUE" (returning value should = 19.5625). Formula: (11*13+21*23)/SUM(11,21)

Please see attached for sample data (there are two different data layouts). Weighted Average Sumproduct Multiple Criteria.xlsb

My actual data set cannot be consolidated further (in which would eliminate the repeating criteria labels)...I tried that approach first, just not an option given the data source.

TIA! Very much appreciated!

Your criteria definition doesn't appear to be consistent in your post.

The value of 11 is associated with "ZP" and not "ZP-TERM." Is this intentional?

Not sure about the first layout, the second one is better

Use
=SUMPRODUCT((B18:B20="ZP")*(C18:C20)*(INDEX(D18:E20,,MATCH("Value",D17:E17,0))))/SUMIF(B18:B20,"ZP",C18:C20)

Change highlighted portions for varying results

You just need a way to reference ZP. I put it in D10:D11.

TERM, F10:
=SUMPRODUCT((($B$3:$B$11=D10)*($C$3:$C$11)),(($B$4:$B$12=D10&"-TERM")*($C$4:$C$12)))/SUMPRODUCT(($B$3:$B$11=D10)*($C$3:$C$11))

VALUE, F11:
=SUMPRODUCT((($B$3:$B$11=D11)*($C$3:$C$11)),(($B$5:$B$13=D11&"-VALUE")*($C$5:$C$13)))/SUMPRODUCT(($B$3:$B$11=D11)*($C$3:$C$11))

I remember the exact document I was working with three years ago the first time someone showed me offsetting ranges.

It was a phoneswitch output, with names on some lines, and stats per person on successive lines, offset 4 rows and 9 columns.

It's a beautiful thing.