# 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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### 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
1
Views
84
Replies
1
Views
87
Replies
12
Views
619
Replies
1
Views
159
Replies
14
Views
865

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,820
Messages
5,772,462
Members
425,760
Latest member
paphon

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