Sumproduct Help

unit213

Active Member
Joined
Jul 11, 2003
Messages
427
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. :rolleyes:

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

Thanks in advance,

Dan

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

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
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
 
Upvote 0
Dan,

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

Sheet2
Book6
ABCD
1PartCompanyConcat
2aFordaFord
3aGMaGM
4bChryslerbChrysler
5cChryslercChrysler
6dForddFord
7dToyotadToyota
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]""
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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
Back
Top