Calculate total cost/time per completed project.

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: (this will ignore B)
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)


Example Data
Project A:

Project Name	Project Stage	Period	Cost	Time Spent
A	Dev	8	105	21
A	Sit	9	108	53
A	Bat	10	94	55
A	Prod	11	103	19
B	Dev	1	66	48
B	Sit	5	70	27
B	Bat	10	53	70
C	Dev	1	90	37
C	Sit	6	94	27
C	Prod	9	48	70

sample output table i'd like to recreate automatically.

Untitled.png

Hi, welcome to the forum

Im really not sure how you arrive at your output table, can you walk us through what you are doing, please?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

See if this is heading in the right direction?

	I	J
1		A
2	Period
3	1	0
4	2	0
5	3	0
6	4	0
7	5	0
8	6	0
9	7	0
10	8	105
11	9	213
12	10	307
13	11	410
14	12	410

J1=Name
J3=IF(COUNTIFS($A$3:$A$12,$J$1,$B$3:$B$12,"Prod")>=1,SUMIFS($D$3:$D$12,$A$3:$A$12,$J$1,$C$3:$C$12,"<="&I3),0)
copied down

Please try at

I4 Press Ctrl+Shift+Enter
=SUM(SUMIFS($F$5:$F$20,$C$5:$C$20,IF(($E$5:$E$20=H4)*($D$5:$D$20="Prod"),$C$5:$C$20)))

J4
=SUMPRODUCT(($E$5:$E$20=H4)*($D$5:$D$20="prod")/COUNTIFS($C$5:$C$20,$C$5:$C$20,$D$5:$D$20,$D$5:$D$20))

Drag I4:J4 down

As you are new here, I shall provide this cross-post link for you this time: https://www.mrexcel.com/forum/excel-...d-project.html

Our rules require you to declare cross-posting, so please do so in future, if you have cause to do it. Thanks.

Keeping us updated is your job, please. Although we ask you to provide the link, there is no expectation that our members should need to follow it in order to help you. Thanks.