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