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

  • Thread starter Thread starter Ajay
  • Start date Start date
A

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
 
=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.
 
Back
Top