Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Which program? Excel? –  minitech 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
1  
Perhaps this? –  Dennis Williamson Jun 2 '12 at 11:15

2 Answers 2

For Excel 2007 or newer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.