Help with Formula
 

I have a spreadsheet with the following:

CompanyA Account# Amount
CompanyB Account# Amount
CompanyC Account# Amount

I would like to use the above spreadsheet to update another spreadsheet
organized as follows:

Company Account 1 Account 2 Account3 (Heading)
CompanyA Amount1 Amount2 Amount3
CompanyB Amount1 Amount2 Amount3
CompanyC Amount1 Amount2 Amount3

I'm not sure how to reference both the Company and the Account to return the
Amount.

Any assistance greatly appreciated.

Help with Formula
 

A B C
1 CompanyA Account# Amount
2 CompanyB Account# Amount
3 CompanyC Account# Amount


A B C D
1 Company Account 1 Account 2 Account3 (Heading)
2 CompanyA Amount1 Amount2 Amount3
3 CompanyB Amount1 Amount2 Amount3
4 CompanyC Amount1 Amount2 Amount3


=SUMPRODUCT(--(Sheet1!$A$1:$A$3=$A2), --(Sheet1!$B$1:$B$3=B$1),
Sheet1!$C$1:$C$3)

Adjust ranges as necessary. Sumproduct cannot accept entire columns as
arguments.

See also:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"lk" wrote:

I have a spreadsheet with the following:

CompanyA Account# Amount
CompanyB Account# Amount
CompanyC Account# Amount

I would like to use the above spreadsheet to update another spreadsheet
organized as follows:

Company Account 1 Account 2 Account3 (Heading)
CompanyA Amount1 Amount2 Amount3
CompanyB Amount1 Amount2 Amount3
CompanyC Amount1 Amount2 Amount3

I'm not sure how to reference both the Company and the Account to return the
Amount.

Any assistance greatly appreciated.