Using SEARCH function within SUMPRODUCT formula

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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???
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Doing text to columns on column B would make this far easier :)

Edit: what am I talking about! Paste down:
Excel Workbook
ABCD
1FZFZ28
2WKWK, ZR47
3ZR9K36
4FZFZ, WK, 9K118
5FZ50
69K60
Sheet1
Excel 2003
Cell Formulas
RangeFormula
D1=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$6,B1))),--($C$1:$C$6))
 
Last edited:

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
Doing text to columns on column B would make this far easier :)

Edit: what am I talking about! Paste down:

Excel Workbook
ABCD
1FZFZ28
2WKWK, ZR47
3ZR9K36
4FZFZ, WK, 9K118
5FZ50
69K60
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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!!!!
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Doesn't...

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

do what you are after then?
 

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
Yes =SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$6&",",B1&",")),$C$1:$C$6) works perfectly for me. Thanks again! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,938
Messages
5,598,959
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top