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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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]""
 

unit213

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

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top