How to calculate summary table for my production

[clindemeier]

Hi,
I'm working on the production of a Microsoft Office e-learning course.

My Excel file contains a worksheet where I track progress for each individual course module. The 4 production stages are (simplified): text - screenshots - voice-over and animation
Whenever a step is completed I currently enter a "1" in the relevant cell.

In order to track overall production progress, I use the dashboard on the 2nd worksheet. The report shows the progress for the overall e-learning course split by product and by production phase.
Currently, the progress is entered manually into the dashboard summary, but I want it to be calculated.
File can be seen here: https://drive.google.com/file/d/0BzlPWR9SUc70dlhGZ1BqQzlLQjA/view?usp=sharing

I tried to play with pivot table but can't get the same view as in my current dashboard.

Can anyone help?

 

[estevaoba]

Hi!

Try this at B4, then copy across and down.

=SUMIF(Source!$C$2:$C$16,$A4,OFFSET(Source!$D$2:$D$16,,COLUMN(A$1)-1))/COUNTIF(Source!$C$2:$C$16,$A4)

Godspeed!

 

[clindemeier]

thanks a lot!!!! Your formula works great!
Carsten