Calculate total cost/time per completed project.

[Vaguely]

Hey all,

I have a set of data I'm trying to calculate the cost / time per completed project. A completed project is one that has gone to "Prod". (production).

I want to figure out a formula that takes the below data and calculates for me the COST (or time) spent on projects that are complete (have "Prod") and tell me what period it was completed in.

It would be amazing to figure out a user friendly / maintainable / scale able way of doing this

Example Final:
Period: 11 -> manually entered
Cost: 410 -> no idea how this work. thoughts (Index + Aggregate) or using pivot table?
Time: 148 -> (same as above)
Projects: 1 -> (Countifs should work here)

Project A:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Project Stage[/TD]
[TD]Period[/TD]
[TD]Cost[/TD]
[TD]Time Spent[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Dev[/TD]
[TD]8[/TD]
[TD]105[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Sit[/TD]
[TD]9[/TD]
[TD]108[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Bat[/TD]
[TD]10[/TD]
[TD]94[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Prod[/TD]
[TD]11[/TD]
[TD]103[/TD]
[TD]19[/TD]
[/TR]
</tbody>[/TABLE]

 

[Vaguely]

There are forum members that can probably set it up with formulas, far better than I....but the VBA will allow for a dynamic number of projects and ranges.

Thanks! i am inserting a "table" because that way it's more scaleable - i hope?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-ge...te-total-cost-time-per-completed-project.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Vaguely]

Cross posted https://www.excelforum.com/excel-ge...te-total-cost-time-per-completed-project.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Thanks, this is extremely helpful reminder. I really appreciate you looking out for my thread. Please visit the other link for anyone else viewing, there's quite a bit of discussion there, although no solution has been found. I am working on one as well.