#### KP_SoCal

##### Board Regular

- Joined
- Nov 17, 2009

- Messages
- 116

Column A, rows 1-5 will have values such as:

A1: FZ

A2: WK

A3: ZR

A4: FZ

A5: FZ

A6: 9K

Column B, rows 1-4 will have values such as:

B1: FZ

B2: WK, ZR

B3: 9K

B4: FZ, WK, 9K

Column C, rows 1-6 will have values such as:

C1: 2

C2: 4

C3: 3

C4: 1

C5: 5

C6: 6

So here’s what I need to do. Using the SUMPRODUCT function, I want to want to find values within range A1:A6 that match value in B1 and them sum the corresponding rows C1:C6 to get my results. The formula would look something like this: =SUMPRODUCT((A1:A6=B1)*(C1:C6). The result for this formula would be 8, which ended up being C1+C4+C5.

This is the easy when I’m looking at cell B1 as my criteria. It gets difficult when B2 becomes my criteria, because I want to look for values WK

*and*ZR when in B1 I was only looking for values for FZ. So how do I adjust my formula to look for all values that are before or after a comma in a given range? The SEARCH function probably holds the key is solving this dilemma, but I'm not sure how to apply it correctly within the SUMPRODUCT function.

There is another posting at http://www.mrexcel.com/forum/showthread.php?t=423973 that is very close to what I need (see DonKeyOte’s response), but I still can’t quite make it work for my scenario. Any ideas???