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

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

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

=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.
--
__________________________________
HTH

Bob

"Ajay" wrote in message
...
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

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

Bob Phillips;232362 Wrote:
=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.
--
__________________________________
HTH

Bob

"Ajay" wrote in message
...
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




In addition to Bob's advice, if you're not too familiar with PT 's (
very interesting for analyzes ) check out 'this link'
(http://peltiertech.com/Excel/Pivots/pivotstart.htm) and also 'this
site' (http://www.contextures.com/tiptech.html) under the letter "P"


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64864

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

Many thanks Bob
Formula worked just what I needed
Thanks
Ajay

"Bob Phillips" wrote:

=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.
--
__________________________________
HTH

Bob

"Ajay" wrote in message
...
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

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

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

--
__________________________________
HTH

Bob

"Ajay" wrote in message
...
Many thanks Bob
Formula worked just what I needed
Thanks
Ajay

"Bob Phillips" wrote:

=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.
--
__________________________________
HTH

Bob

"Ajay" wrote in message
...
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