Working with Arrays
 

I have three columns of data in a worksheet (Name, Report, Usage). In a
separate worksheet, I'd like to construct a matrix with Names in Column A and
Reports in Row 1. I need a function (?) to compare the Report and Name in
the second worksheet to the first and return the Usage.

Example:
Worksheet 1:
Joe Tax Report 5
Joe Income Report 8
Jim Income Report 6
Worksheet 2: (This is what I am trying to do.)
Joe Jim
Tax Report 5
Income Report 8 6
Any help is appreciated

Working with Arrays
 

Assumptions:

A1:A3 contains the name

B1:B3 contains the type of report

C1:C3 contains the amount

F1:G1 contains Joe and Jim

E2:E3 contains Tax Report and Income Report

Formula:

F2, copied down and across:

=SUMPRODUCT(--($A$1:$A$3=F$1),--($B$1:$B$3=$E2),$C$1:$C$3)

Hope this helps!

In article ,
"Judy" wrote:

I have three columns of data in a worksheet (Name, Report, Usage). In a
separate worksheet, I'd like to construct a matrix with Names in Column A and
Reports in Row 1. I need a function (?) to compare the Report and Name in
the second worksheet to the first and return the Usage.

Example:
Worksheet 1:
Joe Tax Report 5
Joe Income Report 8
Jim Income Report 6
Worksheet 2: (This is what I am trying to do.)
Joe Jim
Tax Report 5
Income Report 8 6
Any help is appreciated