aggregating weekly data into monthly

**mattbessey** · 03-24-2010, 01:19 PM

Hey all, I have a problem I could use some help on. I have weekly revenue data for 450 movies and I need to aggregate it into monthly data. I have already assigned unique months to each week of data, but I need help summing the revenues as simply as possible, since I have 7700 rows of data.

Here's my format at the moment:

Column A
unique month indicator

Column C
Weekly revenue data

Column D
Movie title

So, my first few rows look like:

A C D
9501 9930559 Toy Story
9501 34579405 Toy Story
9502 24660317 Toy Story

What I would like to do:
Sum column C for all weeks that have the same indicator in A and D

ie, there may be a few repeats of 9501 (week one of '95), but I only want to add together those that are from the same movie.

I was trying to use sumif, but that only let's me match one criterion. And I got a little confused when I tried a nested if. Can anyone help?

My thought was to make a new sheet with unique weekly indicators down the first column (9501, 9502, 9503...) and unique titles across the first row, and then have each cell in the grid sum the rows that matched the heading on the row and column. Anyone know how to program this or have an easier way?

Thanks a lot guys.

**tony h** · 03-24-2010, 01:37 PM

depending on what you are intending doing with the data afterwards the SubTotal function on the Data tab should do it.

**DonkeyOte** · 03-24-2010, 02:12 PM

Put simply, use a Pivot Table - general intro link can be found in my sig.

On an aside given your use of XL2008 formula wise check out SUMIFS though note it is not backwards compatible ... to reiterate though a Pivot Table would seem to be best fit.

**mattbessey** · 03-24-2010, 02:14 PM

this does almost exactly what I need it to, but it would be perfect if there was a way to extract all of the subtotals to a new row, so they're not dispersed amongst 7700 rows of data.

Any way to do this? Thanks so much for the quick response btw.