Using SEARCH function within SUMPRODUCT formula

[KP_SoCal]

This is somewhat complicated. You are a super-genius in my book if you can figure it out. Here goes the explanation…

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???

 

[taigovinda]

Doing text to columns on column B would make this far easier

Edit: what am I talking about! Paste down:

Excel Workbook
	A	B	C	D
1	FZ	FZ	2	8
2	WK	WK, ZR	4	7
3	ZR	9K	3	6
4	FZ	FZ, WK, 9K	1	18
5	FZ		5	0
6	9K		6	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$6,B1))),--($C$1:$C$6))

 

[KP_SoCal]

Doing text to columns on column B would make this far easier

Edit: what am I talking about! Paste down:

Excel Workbook
	A	B	C	D
1	FZ	FZ	2	8
2	WK	WK, ZR	4	7
3	ZR	9K	3	6
4	FZ	FZ, WK, 9K	1	18
5	FZ		5	0
6	9K		6	0
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$6,B1))),--($C$1:$C$6))

I agree, text to columns is way easier but the process is still entirely manual and requires you to create additional columns on your spreadsheet. It's not practical for what I am working on. The formula you provided works beautifully!!! Thanks so much!!!!

 

[Aladin Akyurek]

Doesn't...

=SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$6&",",B1&",")),$C$1:$C$6)

do what you are after then?

 

[KP_SoCal]

Yes =SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$6&",",B1&",")),$C$1:$C$6) works perfectly for me. Thanks again!