Sum If formula

S

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question on formula of matching Project Workload vs Resource Capac 2
SUMIF With Variable Consecutive Days 6
Sum cells using criteria from a row and a column 2
IF formula results not displaying 1
Subtotal - SUM, COUNT NUMBER, 'Live' Formula 5
Automatically add and subtract 3
Sum Products with VLOOKUP 3
Help with If Sum statement - i think.... 4

Top