Sum If formula

[Suan]

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

 

[Ashish Mathur]

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