SUMPRODUCT of text values where each text value corresponds to a number in a lookup table

[sdetwiler19]

I'm trying to perform a SUMPRODUCT of 2 columns where each value is text, but those text values correspond to numbers in a lookup table. There's an easy way to do this using hidden columns that perform the VLOOKUP part, but I'm trying to avoid any hidden columns.

I know that the following formula doesn't work, but it best illustrates what I'm trying to do:

=SUMPRODUCT(VLOOKUP($C$10:$C$24,PRIORITY_SCORE,2,FALSE)*VLOOKUP($E$10:$E$24,RATING_SCORE,2,FALSE))

Here's an example of the data:

[TABLE="width: 205"]
<tbody>[TR]
[TD]C[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Critical[/TD]
[TD]Exceeds[/TD]
[/TR]
[TR]
[TD]Critical[/TD]
[TD]Meets[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Limited[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]None[/TD]
[/TR]
</tbody>[/TABLE]

The PRIORITY_SCORE reference looks like this:

[TABLE="width: 150"]
<tbody>[TR]
[TD]Low[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Medium[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Critical[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

The RATING_SCORE reference looks like this:

[TABLE="width: 150"]
<tbody>[TR]
[TD]None[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Limited[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Meets[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Exceeds[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

So the expected output would be as follows:

[TABLE="width: 350"]
<tbody>[TR]
[TD]C[/TD]
[TD]E[/TD]
[TD]product[/TD]
[/TR]
[TR]
[TD]Critical = 4[/TD]
[TD]Exceeds = 3[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Critical = 4[/TD]
[TD]Meets = 2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]High = 3[/TD]
[TD]Limited = 1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Medium =2[/TD]
[TD]None =0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Formula output = 23

Any thoughts or suggestions would be much appreciated!

 

[Andrew Poulsom]

Does this work for you?

=SUMPRODUCT(SUMIF(INDEX(PRIORITY_SCORE,0,1),C10:C24,INDEX(PRIORITY_SCORE,0,2)),SUMIF(INDEX(RATING_SCORE,0,1),E10:E24,INDEX(RATING_SCORE,0,2)))

 

[sdetwiler19]

Perfect! Thanks Andrew!

 

[Peter_SSs]

Welcome to the MrExcel board!

No problem with the suggested formula, but just a couple of comments/ideas to consider.

1. Because the two columns of your named ranges are distinctly text:numbers you could shorten the formula a bit by not indexing to get the first part of each SUMIF. So I think this should do the same job:

=SUMPRODUCT(SUMIF(PRIORITY_SCORE,C10:C24,INDEX(PRIORITY_SCORE,0,2)),SUMIF(RATING_SCORE,E10:E24,INDEX(RATING_SCORE,0,2)))



2. If you have introduced the named ranges to help with this calculation, it may be worth expending a little more effort to name each column of those two existing ranges separately. Say
PS_1 (for Priority Score col 1 etc)
PS_2
RS_1
RS_2

By doing that, your formula simplifies (& shortens) again to

=SUMPRODUCT(SUMIF(PS_1,C10:C24,PS_2),SUMIF(RS_1,E10:E24,RS_2))

 

[sdetwiler19]

Hi Peter_SSs,

Thanks for the simplification! That works perfectly as well. I separated the named ranges as you suggested. Here's where I ended up:

{=SUMPRODUCT(SUMIF(PRIORITY,$C$10:$C$31,PRIORITY_SCORE),SUMIF(RATING,D$10:D$31,RATING_SCORE))/SUM(SUMIF(PRIORITY,$C$10:$C$31,PRIORITY_SCORE))}

The second piece of this divides by the sum of the values corresponding to the priorities, in order to get a weighted average score. I haven't seen this behavior with SUMIF before where the result is an array, but looks like a useful function!

Thanks to both of you for your help!

 

[Peter_SSs]

Thanks to both of you for your help!

You are welcome.