How can I get an average with a data set that needs to be grouped and summed first?

TianaJones

Hello! I have a smartsheet that records the status each project is in and then calculates the number of days each project spent in that status. There can be any number of status changes per project. I need to be able to average how long each project took and I'm struggling! I made a sample dataset below and showed the manual math I need. I can't just average all the statuses, because that just shows the average time per status. I need to sum each project and then average those numbers. (The average of 16 in my sample data)

Project #	Status	# of Days in Status		Project 1	Project 2	Project 3
1	Team 1	4		4	1	8
1	Team 2	5		5	2	6
2	Team 1	1		9	2	8
2	Team 2	2		3	5	22
3	Team 1	8		21
3	Team 2	6
1	Team 3	9			Average of totals		16
2	Team 3	2
1	Team 4	3
3	Team 3	8
Average of statuses		4.8

Thank you!!

Sam_Harwart

Hi there,

So you want to total the time for each project? You could use a SUMIF().

=SUMIF([Project #]:[Project #], 1, [Days In Status]:[Days In Status])

This formula looks at the Project # column, finds each time "1" is found, and then adds the # of days in the status column for that row. You'd update the "1" in the middle of the formula to 2, 3, etc. for whichever project you're looking at.

Let me know what you think!