Calculating hours spent per team

[The Fool on the Hill]

Dear Excel(lent) users,

I have a challenge, which I would like to share with you:

I am managing multiple projects and have multiple teams working on these
projects. I want to know how much time is spent on what project by what team.

Column A = Project
Column B = Team (e.g. team 1, team 2, team 3, team 4)
Column C = number of hours spent

Can you please give me formula which gives me the desired information?

Thank you kindly !!

Jaydubs !!

 

[Pete_UK]

On a separate sheet you could lay out a table like this:

Team1 Team2 Team3 etc
Project1
Project2
Project3
etc

and then in B2 you could have this formula:

=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1)*(Sheet1!
$C$1:$C$100))

then copy across and down as required.

You could also use a Pivot Table for this.

Hope this helps.

Pete