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

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
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,209
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
 
Master Excel Bundle

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.

Forum statistics

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