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:

C	E
Critical	Exceeds
Critical	Meets
High	Limited
Medium	None

<tbody>
</tbody>

The PRIORITY_SCORE reference looks like this:

Low	1
Medium	2
High	3
Critical	4

<tbody>
</tbody>

The RATING_SCORE reference looks like this:

None	0
Limited	1
Meets	2
Exceeds	3

<tbody>
</tbody>

So the expected output would be as follows:

C	E	product
Critical = 4	Exceeds = 3	12
Critical = 4	Meets = 2	8
High = 3	Limited = 1	3
Medium =2	None =0	0

<tbody>
</tbody>

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.