Countif or sumproduct or VLOOKUP......Help

[Ajay]

Hi All

I have a sheet relating to procedures carried out within 40 different
laboratories.
Format Column headings:
Column A B C D
Calibration Procedure, Laboratory, Gage Type, No of Records In 2008

I have identified the unique procedures from the raw data approx 300 my next
step is to list these unique entries in column A. List the unique lab names
across the columns and insert the Number of records in 2008 from the raw data
table into this new summary table.

EG:
Procedure Lab 1 Lab2 Lab3
ABC 10 20 30
XYZ 50 60 70

My question is what function do I use? I need to lookup values in both
column A and then match with Column B and list the figure in Column D. Could
I do this with a Pivot table? Not had much experience with these.

Any advice would be appreciated
Ajay

 

[Bob Phillips]

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

or you could use a pivot, the data seems conducive.

Just select all of the data, and follow thw PT wizard. Then drag the items
from the PT pane to the PT, Procedure to row, labaoratory to column, no of
records to data.

 

[Ajay]

Many thanks Bob
Formula worked just what I needed
Thanks
Ajay

 

[Bob Phillips]

Take a look at pivots as well Ajay, you might find it very useful in future.