CODEWITHSUNDEEP
excelvba
Jack
Jack

Reputation: 1240

Sum numbers by date and by ID in Excel

I have a very large (lets say, 100,000 rows) excel table that has some (there are others) columns in it:

Person ID |   Date   | Number |
0043       03/20/2006  6
0043       03/20/2006  3.21
1478       04/01/2006  9.87
0021       04/12/2006  0.23
0101       04/12/2006  3.14
1042       04/12/2006  11.13

What I would like to do is go through the table and add up the numbers, by date, for the given person ID. Meaning: right now some entries are listed twice for the same date, but I would really like a "sum" of those number values for that person on that date (not two separate rows).

Later, I am going to run some analysis on those "summed results", like, "how many people have a number over 3 but under 6", etc.

Is there a simple function I can run that will sum up the values by date by person ID, and give me a new column to display the resulting sum in? OR, is there a VB program that will do this with ease?

Ultimately, I'm hoping to have a new column to display the results in, this new table would look like the following:

Person ID |   Date   | Number | Total Per ID Per Date |
0043       03/20/2006  6        9.21
0043       03/20/2006  3.21     N/A
1478       04/01/2006  9.87     9.87
0021       04/12/2006  0.23     0.23
0101       04/12/2006  3.14     3.14
1042       04/12/2006  11.13    11.13

Any thoughts/suggestions? Visual Basic or chain an excel function? For simplicity of given answers, lets assume Columns (Person ID, Date, etc) are A-C (or A-D, in the case of the resulting table) and of course rows 1-7.

Thank you!

Upvotes: 0

Views: 3066

Answers (1)

chuff
chuff

Reputation: 5866

As suggested by Doug Glancy, what you are looking for is relatively easy to produce with a pivot table. Here's one way to do it.

First, to preserve the original order of your data, add a column labeled Sort to your data table, say in column E, and put in an ascending index number, incrementing by 1 for each new PersonID. Thus, the sort number in cells E2 & E3 would be 1, E4 would be 2, etc. This is easily enough done with a formula, e.g., "if PersonID in this row = Person ID in the previous row, then Sort number in this row = Sort number in the previous row, else Sort number n this row = Sort number in previous row + 1.

Create a pivot table for your data. This can be done by selecting one of the cells in the data table, then choosing Insert, Pivot Table from the ribbon. Put the pivot table in a new worksheet.

In the 'PivotTable Field List' in the new worksheet, set Sort, PersonID, Date, and Number as Row Labels and set Number as the Values field (this should show as a 'Sum of Number' column in the pivot table).

From the Design tab of the PivotTable Tools bar:

  • Set the pivot table to 'Tabular Form' (Report Layout)
  • Turn off subtotals (Subtotals)

Make the PersonID and Date fields repeating, by right-clicking in those columns in the pivot table and choosing the appropriate option in 'Field Settings'/'Layout and Print'.

You can get the final Total column you want with a bit of a trick:

  • Set the 'Sum of Number' column to show values as a percent of row total (right click in the column and select that option).
  • Add a new column to the pivot table by entering the label "Total per ID per Date" in cell F3. (By default, the pivot table is in B3:E9).

  • In cell F4, enter the following formula and then copy it down to cell F9.

    =IF(AND(B4=B3,C4=C3),"NA",D4/E4)

    This formula calculates the totals for each PersonID, returning "NA" if the PersonID and Date repeats.

  • Finally, hide the Sort column and the Sum of Number column.

Upvotes: 1

Related Questions