SumProduct

[Emoncada]

I have a list of items going down column A.
Row 1 I have 6 different companies.
That's in sheet2, I want data from sheet one to be pulling into
the correct cell. Example
Looking a sheet1 column D is the Item, column G is the company.
I would like for it to fall into place and add the number in Column E
example

Sheet1

.....D.............E...........F............G
Printer...........4.........Yes..........HP
Monitor..........2.........No............CQ
Printer...........1.........Yes...........HP

Sheet2
...................B................C..............D

..................CQ..............HP...........HS
Hard Drive....0................0..............0
Printer..........0................5..............0
Monitor.........2................0..............0

 

[jungix]

You should use a pivot table

 

[Emoncada]

I want to refresh data all the time is there a way to automate that.

 

[Emoncada]

Is there a Sumproduct type of formula that can do this

 

[Todd Bardoni]

Given your example:-

=SUMPRODUCT(--($B2=Sheet1!$D$2:$D$4),--(C$1=Sheet1!$G$2:$G$4),Sheet1!$E$2:$E$4)

 

[Emoncada]

In what cell would you put that in.

 

[Emoncada]

sheet2 to has no empty row 2 just incase.

 

[Emoncada]

I GOT IT. Sorry typo