Cumulative Total & Percentage

[Datatellsall2]

Hello -

I'm looking to calculate cumulative totals and percentages based on the type of data in column A (unique company code identifier) and column B (date of time entry). My time data is in column C (formatted as an integer). I would only like to calculate the cumulative total & % if the row data agrees to the unique identifier. I have roughly 5000 lines of data. Should I be utilizing a mix of Vlookup and Sum functions here?

Unique Identifier	Date	Time Amount (hrs)	Cumulative Total	Cumulative %
100	1/1/18	3
100	1/2/18	4
100	1/5/18	5
101	1/1/18	2
101	1/9/18	3
105	1/21/18	10
105	1/22/18	8

<tbody>
</tbody>

Any help would be greatly appreciated.

Thanks,
M

 

[sheetspread]

The pivot table does this unless you absolutely need formulas:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Unique Identifier	Date	Time Amount (hrs)	Cumulative Total	Cumulative %			Unique Identifier	Date	Sum of Time Amount (hrs)	Sum of Time Amount (hrs)2	Sum of Time Amount (hrs)3
2	100	1/1/2018	3					100	1/1/2018	3	3	8.57%
3	100	1/2/2018	4					100	1/2/2018	4	7	20.00%
4	100	1/5/2018	5					100	1/5/2018	5	12	34.29%
5	101	1/1/2018	2					101	1/1/2018	2	2	5.71%
6	101	1/9/2018	3					101	1/9/2018	3	5	14.29%
7	105	1/21/2018	10					105	1/21/2018	10	10	28.57%
8	105	1/22/2018	8					105	1/22/2018	8	18	51.43%
Sheet12

 

[fhqwgads]

like this?

ID    	Date	       hrs     	Cum. Total
100	1/01/2018	3	3
100	1/02/2018	4	7
100	1/05/2018	5	12
101	1/01/2018	2	2
101	1/09/2018	3	5
105	1/21/2018	10	10
105	1/22/2018	8	18

sorry for the poor formatting i cant seem to get the mrexcel html maker to work for me
this is the formula in the first cell under cumulative total
=C2+IFERROR(LOOKUP(2,1/($A$1:$A1=A2),$D$1:$D1),0)

 

[sheetspread]

This works too:

Excel 2010
	A	B	C	D	E
1	Unique Identifier	Date	Time Amount (hrs)	Cumulative Total	Cumulative %
2	100	1/1/2018	3	3	8.57%
3	100	1/2/2018	4	7	20.00%
4	100	1/5/2018	5	12	34.29%
5	101	1/1/2018	2	2	5.71%
6	101	1/9/2018	3	5	14.29%
7	105	1/21/2018	10	10	28.57%
8	105	1/22/2018	8	18	51.43%
Sheet12

Cell Formulas
Range	Formula
D2	=SUMIF($A$2:A2,A2,$C$2:C2)
E2	=D2/SUM($C$2:$C$8)

 

[Datatellsall2]

Thank you! This is almost what I need. The cumulative total formula answers my question for that column. I'm unable to get the right % for cumulative % based on this equation: =D2/SUM($C$2:$C$8), because it doesn't reference my unique identifier. Do I need to add a LOOKUP function to this?

Thanks again,
M

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E
1	Unique Identifier	Date	Time Amount (hrs)	Cumulative Total	Cumulative %
2	100	1/1/2018	3	3	25.00%
3	100	1/2/2018	4	7	58.33%
4	100	1/5/2018	5	12	100.00%
5	101	1/1/2018	2	2	40.00%
6	101	1/9/2018	3	5	100.00%
7	105	1/21/2018	10	10	55.56%
8	105	1/22/2018	8	18	100.00%
Sheet12

Cell Formulas
Range	Formula
D2	=SUMIF($A$2:A2,A2,$C$2:C2)
E2	=D2/SUMIF($A$2:$A$8,A2,$C$2:$C$8)

 

[Datatellsall2]

Perfect! That answers my question. Thank you very much.

M