SUM Values based off Lookup Table

I'd like to SUM all values in DATA TABLE col_b where DATA TABLE col_a has a value of MED in the LOOKUP TABLE.

For example using the tables below..
I want to sum col_b in the data table for all Cat# that have a lookup of HIGH. This would be Cat1, Cat2, and Cat5. Thus, equalling: 5+3+9+8 = 25

Any ideas on how to get this to work?

LOOKUP TABLE
    col_a    col_b
    Cat2     HIGH
    Cat5     HIGH
    Cat3     MED
    Cat4     LOW
    Cat1     HIGH

DATA TABLE
    col_a   col_b
    Cat1     5
    Cat1     3
    Cat3     8
    Cat1     9
    Cat5     1
    Cat4     3
    Cat2     8 
    Cat3     3

Thanks,
JC

If you list your Lookup_table with column a sorted alphabetically as per your sample, then....

Assuming Lookup_table is in A1:B6, incl. headers and Data_table is in D1:E9, then try:

=SUMPRODUCT(--(LOOKUP($D$2:$D$9,$A$2:$B$6)="High"),$E$2:$E$9)

you can replace "High" with a cell reference.

LOOKUP() is one of the few functions that will allow you to build an array of text results within a formula so that you can compare and match to other tables... Lookup() unfortunately requires lookup data to be listed in sorted ascending order to work properly.

I am trying to think of something else, but it is late in the day and my brainpower is going fast... I see other eyes on this thread and perhaps they will have something that will not require a helper column....

Okay give this a try.. with the same assumptions, except lookup table doesn't need to be in order....

=SUMPRODUCT(--(ISNUMBER(MATCH($D$2:$D$9&"High",$A$2:$A$6&$B$2:$B$6,0))),$E$2:$E$9)

again, you can replace "High" with a cell reference

Hello carlyman,

See the attached with DSUM formula.

I would try:

=SUMPRODUCT(--(ISNUMBER(MATCH($D$2:$D$9&"HighType",$A$2:$A$6&$B$2:$B$6&$F$2:$F$6,0))),$E$2:$E$9)

for better robustness, perhaps add a special symbol between so that there is no accidental overlaps...

e.g.

=SUMPRODUCT(--(ISNUMBER(MATCH($D$2:$D$9&"_High_Type",$A$2:$A$6&"_"&$B$2:$B$6&"_"&$F$2:$F$6,0))),$E$2:$E$9)

The underscore makes definitive separations in the words in case there may be an dubious overlap.

I assumed the column F: Type is in the Lookup_Table....

Also that you are looking for the word Type in column F in order for it to be a match....

Can you repost a sample workbook with the added information and expected results?

Try, in H11:

=SUMPRODUCT(--(ISNUMBER(MATCH($A$12:$A$21&F11,$A$2:$A$6&$B$2:$B$6,0))),--($B$12:$B$21=G11),$C$12:$C$21)

copied down