Daily data automatically totaled in new sheet without dragging / re-adding.

Hello excelforum!

I think I have an easy one for ya today. Each day I get a set of data showing the city an item came from and how many items came from that city. Each day I put this data into a workbook underneath the previous day's data. So sheet 1 looks like this:

Day 1
city # #
city # #
city # #

Day 2
city # #
city # #

Day 3
city # #
city # #
city # #
city # #
city # #
city # #

This continues through the whole month. Each day could have 2 entries, or 20 entries; some of which repeat daily, and others that don't. My question is; is it possible to have sheet 2 contain a master list of all the cities, and each time I paste another day into sheet 1 it updates sheet 2's totals?

Right now if I want to get a monthly total I have to copy and paste each day into a new sheet, then sort it all by city then use the subtotal function, then copy and paste that data, then delete all the blank rows, then make it pretty. I'm thinking there is a better way that I just don't see.

Example workbook attached.

Thank you in advance for all the help.

I would reccommend restructuring your original table, so that each line has the date, place, and volume.
A Pivot table becomes very easy to implement.
I also added a filter, so that you can either select all (to View April's Totals), or select a specific day.

Ensure that column 1 does not have any blanks in it, that is ensure that all the rows have a date in them. Then you can easily use pivot tables on a different sheet. Each time you update data in the sheet, you refresh the pivot table and the totals update.

Won't take long at all. You could make formulas to do all of this, but it would take time, and the formatting would not automatically adjust instantly.
Assuming you are in Excel 2010, I would also suggest reading up on Slicers. Makes the filtering of pivot tables that much better.

And also dynamic named ranges, so you dont have to change the pivot source data each time. You can just refresh and it auto updates.

Definitely true arlu.
I'm not quite comfortable with dynamic named ranges yet. I find a good alternative is to simply "Format as Table".