Evaluating commonality across multiple datasets

Hi - I'm trying to compare the similarity in two datasets, and am trying to produce formula (without helper columns) that i) count the number of discrete entries that are also common in two ranges, and ii) sums a corresponding value for entries common in two ranges. Attached is an example worksheet. I'm assuming either a sumproduct-countif/sumif might work, but don't seem to be having much luck. Also the datasets are quite large, which also makes countif challenging. I'd be grateful for any help!

I think you are looking for
=SUM(SUMIF(B4:B16,{"AAA","BBB","DDD"},C4:C16))/SUM(C4:C16)

=SUM(SUMIF(B:B,{"AAA","BBB","DDD"},C:C))/SUM(C:C) - will be the same.

Thanks BMV, although this doesn't seem to work for me. The array needs to be defined as a range, rather than a set number of options (since there are hundreds of discrete values), something along the following lines (but this doesn't seem to work);

=SUM(SUMIF(B:B,{C:C},C:C))/SUM(C:C)

Also, this helps with the sum of common entries, but not the discrete count. Any thoughts? Thanks again!

This doesn't seem to be clear what you would like to calculate. {"AAA","BBB","DDD"}, -the list of values and if you place it to the range X1:X3 then =SUM(SUMIF(B:B,X1:X3 ,C:C))/SUM(C:C) and finish formula CTRL+SHIFT+ENTER

Unfortunately this formula produces a sum that exceeds the actual sum of the column. The goal here is to be able to say "x% of range 1 entries also appear in range 2".

Excellent! Many thanks, this works perfectly.

=COUNT(MATCH(B4:B16,D4:D16,)) - Array formula for I20
or
=SUMPRODUCT(SIGN(COUNTIF(D4:D16,B4:B16)))
for i21
=SUMPRODUCT(SIGN(COUNTIF(D4:D16,B4:B16))/COUNTIF(B4:B16,B4:B16))
or
=SUMPRODUCT((COUNTIF(D4:D16,B4:B16)>0)/COUNTIF(B4:B16,B4:B16))
and for i22
=SUMPRODUCT(SIGN(COUNTIF(D4:D16,B4:B16))*C4:C16)/SUM(C4:C16)
or
=SUMPRODUCT((COUNTIF(D4:D16,B4:B16)>0)*C4:C16)/SUM(C4:C16)

Ace_XL Why so many brackets
=SUMPRODUCT(ISNUMBER(MATCH(B4:B16,D4:D16,))/COUNTIF(B4:B16,B4:B16))
=SUMPRODUCT(ISNUMBER(MATCH(B4:B16,D4,D16,))*C4:C16)/SUM(C4:C16)