0

I have a spreadsheet with 2 cols of importance. Date, and number. I can't always predict the number of rows or the date, but what I would like to do is print out the sum of the numbers for each date.

For example, there might be 5 rows for Dec-7: 200, 111 and Dec-6: 222,533,100. I am tying to create a list which would show Dec-6: 855, Dec-7: 311.

I believe a Pivot Table is what I want but I can't quite figure out how I need to configure it to show what I want. If anyone knows of a guide I could look at that would be fantastic!

Improve this question

1 Answer 1

Reset to default
1

You could use a Pivot Table, but alternatively you could use SUMIF().

For the Pivot Table you would select all your data, click Insert -> Pivot Table, choose where to put the table. You will then have the Pivot Table options appear. Check the boxes for Date and Number fields to add to the report. Your Row Labels should then be Date and your Values should be Sum of line (which is probably the default. If not then click the dropdown arrow on the Number field -> Value Field Settings, then choose Summarize values by -> Sum.

For the SUMIF() option you would get a list of all the dates you want to get totals for, lets say that's in Column C, your dates are in column A and your numbers are in column B. So then in Column D you'd enter the formula =SUMIF(A:A,A1,B:B) then copy that down to the bottom of your list of dates.

Improve this answer
2
  • When I select more than 1 column I get a message saying "Data source reference is not valid." at the part where you say check the boxes for Date and Number. I am confused about how to get the numbers in the table. As of now the dates show, just no sum for each date.
    – user70134
    Commented Dec 7, 2012 at 12:25
  • There are few things to remember in setting up the Pivot Table. First, you can't have any blank columns in the data you're selecting. Second the data has to be in one range - so the columns must be next to each other. If there is data in that range that you don't want in the Pivot Table that's fine, just don't check the checkbox for it. It helps if your columns have headers so teh checkbox names make sense. Let me know if these sort your problem out.
    – Jamie Bull
    Commented Dec 7, 2012 at 15:04

You must log in to answer this question.