How do I set up a table that:

In one spread sheet(Spreadsheet A) I have a list of activities eg planning,
design, analysis etc in a column. second column I have a list of resource
type eg business analyst, project manager, sme etc, inthe next few coulmns I
have months and in those months I have days worked in the month for the
resource type and the activity, they are working on.
I have a table (Table B)in another tab that lists the activity down a coulmn
and the type of resource across a row, so it is very similar to a matrix that
sums all the days by the activity by the resource type. What formula could I
use to pull this info from the first speadsheet(Spreadsheet A) to sum all the
days by activity and resource type.? see example :
Table B
Activity Ba Project Manager Sme
Planning 32 16 10
Analysis 100 5 12
Design 55 21 10
--


Thanks
SC

Hi,

Try this

=sumproduct((sheetA!$A$2:$A$100=$A2)*(sheetA!$B$2: $B$100=B$1)*(sheetA!$C$2:$O$100))

A2 abd B1 on sheetB hold Planning and Ba respectively

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Suan" wrote in message
...
How do I set up a table that:

In one spread sheet(Spreadsheet A) I have a list of activities eg
planning,
design, analysis etc in a column. second column I have a list of resource
type eg business analyst, project manager, sme etc, inthe next few coulmns
I
have months and in those months I have days worked in the month for the
resource type and the activity, they are working on.
I have a table (Table B)in another tab that lists the activity down a
coulmn
and the type of resource across a row, so it is very similar to a matrix
that
sums all the days by the activity by the resource type. What formula could
I
use to pull this info from the first speadsheet(Spreadsheet A) to sum all
the
days by activity and resource type.? see example :
Table B
Activity Ba Project Manager Sme
Planning 32 16 10
Analysis 100 5 12
Design 55 21 10
--


Thanks
SC

Hi
Thanks for that I tried the formula but its returning - #NA ?
--
SC


"Ashish Mathur" wrote:

Hi,

Try this

=sumproduct((sheetA!$A$2:$A$100=$A2)*(sheetA!$B$2: $B$100=B$1)*(sheetA!$C$2:$O$100))

A2 abd B1 on sheetB hold Planning and Ba respectively

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Suan" wrote in message
...
How do I set up a table that:

In one spread sheet(Spreadsheet A) I have a list of activities eg
planning,
design, analysis etc in a column. second column I have a list of resource
type eg business analyst, project manager, sme etc, inthe next few coulmns
I
have months and in those months I have days worked in the month for the
resource type and the activity, they are working on.
I have a table (Table B)in another tab that lists the activity down a
coulmn
and the type of resource across a row, so it is very similar to a matrix
that
sums all the days by the activity by the resource type. What formula could
I
use to pull this info from the first speadsheet(Spreadsheet A) to sum all
the
days by activity and resource type.? see example :
Table B
Activity Ba Project Manager Sme
Planning 32 16 10
Analysis 100 5 12
Design 55 21 10
--


Thanks
SC

Hi,

Post the data and formula you are using

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Suan" wrote in message
...
Hi
Thanks for that I tried the formula but its returning - #NA ?
--
SC


"Ashish Mathur" wrote:

Hi,

Try this

=sumproduct((sheetA!$A$2:$A$100=$A2)*(sheetA!$B$2: $B$100=B$1)*(sheetA!$C$2:$O$100))

A2 abd B1 on sheetB hold Planning and Ba respectively

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Suan" wrote in message
...
How do I set up a table that:

In one spread sheet(Spreadsheet A) I have a list of activities eg
planning,
design, analysis etc in a column. second column I have a list of
resource
type eg business analyst, project manager, sme etc, inthe next few
coulmns
I
have months and in those months I have days worked in the month for the
resource type and the activity, they are working on.
I have a table (Table B)in another tab that lists the activity down a
coulmn
and the type of resource across a row, so it is very similar to a
matrix
that
sums all the days by the activity by the resource type. What formula
could
I
use to pull this info from the first speadsheet(Spreadsheet A) to sum
all
the
days by activity and resource type.? see example :
Table B
Activity Ba Project Manager Sme
Planning 32 16 10
Analysis 100 5 12
Design 55 21 10
--


Thanks
SC