Hello,

I am looking for a formula that will look at a range of cells (on a summary page) and count 1 each time it finds an instance of any word from a list of words in a table (on a data page), then multiply the sum of the multiple instances by their lookup value in the table. The range of cells in the summary page are an unsorted list with blank cells within the range (have typed in null in the blanks).

Here is the formula I've used, but it has some problems: =sumproduct(lookup(col_1,table,dep_1)

Based on the example below, this returns 13, and would seem to work. However, lookup is not reliable with an unsorted list and if I change the last word in column F from cat to riz, it adds 3 to the 13 (assuming it's grabbing the last value in the lookup). Am I using the right functions? Is there a better, more reliable, solution? Please help before my brain explodes.

Example:

Table A3:B7 = defined name as table
Column A
cat
dog
null
dog and cat
pig

Column B = defined name as dept_1
1
2
0
0
3

Column F = defined name as col_1
dog
cat
cat
dog
null
cat
dog
pig
cat