# How to calculate running totals of subsets of data in a table

I have 4 columns: Name, Week, Batch and Units Produced (Cols, A,B,C,D). In column E, I need to keep running totals based on name and week. When the week changes for the same person, restart the total.

``````Fred, 12, 4001, 129.0    Answer in e:  129.0
Fred, 12, 4012, 234.0    Answer in e:  363.0
Fred, 13, 4023, 12.0     Answer in e:  12.0

John, 12, 4003, 420.0    Answer in e:  420.0
John, 13, 4021, 1200.0   Answer in e:  1200.0
John, 13, 4029, 120.0    Answer in e:  1320.0
``````

I need to be able to copy the formula to over 1000 rows.

-
Which program? Excel? – Ryan O'Hara Jun 2 '12 at 3:22
1-2-3?​​​​​​​​​ – Synetech Jun 2 '12 at 3:45
Windows? Linux? OS X? Perhaps Multiplan on C-64? – Dennis Williamson Jun 2 '12 at 11:08
Perhaps this? – Dennis Williamson Jun 2 '12 at 11:15

E1: `=SUMIFS(D\$1:D1,A\$1:A1,A1,B\$1:B1,B1)`

copy down.

.

In case you need the gaps between sections:

E1: `=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",SUMIFS(D\$1:D1,A\$1:A1,A1,B\$1:B1,B1))`

and copy down.

You may change the `OR` to `AND` as required.

.

.

For earlier versions:

F1: `=A1&B1`

E1: `=SUMIF(F1:F\$1,A1&B1,D1:D\$1)`

copy both down.

For the gaps follow the above logic.

-

If you're using Excel 2007 or later, you can take advantage of two built-in features for this.

1. Convert your data to an Excel table.
2. Use the table to create a pivot table.
3. Use the Name and Week columns as row labels in the pivot table.
4. Use Units Produced twice in your values area. Keep one as default and set the other for running total.

Whenever your table changes with updated data, simply refresh your pivot table and you'll have constantly up-to-date running totals.

-