# Sumproduct Help

#### unit213

##### Active Member
I always have problems with the sumproduct function. I need some assistance. I have two variables to look up and return an "X" if true. I want the sumproduct function to tell me which companies use which part. I have all the columns/rows set up as follows. I just can't get sumproduct to return an X for me.

Worksheet 1

Part Description Ford GM Chrysler Toyota
a red X X
b yellow X
c green X
d blue X X

Worksheet 2 looks like this:

Part Company
a Ford
a GM
b Chrysler
c Chrysler
d Ford
d Toyota

Dan

P.S. Let me know if I can clarify. It's difficult describing the situation.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### fairwinds

##### MrExcel MVP
Hi,

I would prefere to MATCH.

Enter formula with CTRL + SHIFT + ENTER in C2 and drag.
Book3
ABCDEFGHI
1PartDescriptionFordGMChryslerToyotaPartCompany
2aredXX  aFord
3byellow  X aGM
4cgreen  X bChrysler
5dblueX  XcChrysler
6dFord
7dToyota
Sheet2

##### MrExcel MVP
Dan,

This is not a problem for which you need a SumProduct formula.

Sheet2
Book6
ABCD
1PartCompanyConcat
2aFordaFord
3aGMaGM
4bChryslerbChrysler
5cChryslercChrysler
6dForddFord
8
Sheet2

Since you have a kind of retrieval problem, we create an additional column using concatenation...

The formula in C2 is:

=A2&CHAR(127)&B2

Sheet1
Book6
ABCDEF
1PartDescriptionFordGMChryslerToyota
2ared1100
3byellow0010
4cgreen0010
5dblue1001
Sheet1

The formula in C2, which is copied across then down, is:

=--ISNUMBER(MATCH(\$A2&CHAR(127)&C\$1,Sheet2!\$C\$2:\$C\$7,0))

Note that this formula outputs a 1 where you want X and a 0 where you want a blank. This is on purpose. You can compute on such numeric results much easily than on textual output. For display purposes, select C2:F5 and apply the following custom format:

[=1]"X";[=0]""

#### unit213

##### Active Member
You guys are incredible. Thanks for the help. Problem resolved. I've never used the match formula before. I'll add it to my collection.

Thanks again!

Dan

Replies
17
Views
274
Replies
15
Views
246
Replies
9
Views
166
Replies
10
Views
373
Replies
1
Views
62

1,186,173
Messages
5,956,367
Members
438,248
Latest member
Poysenberries

### 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.

### Which adblocker are you using?

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

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