Monthly we download data for cities and states. Column A is City, Column B is St
ID: 3562769 • Letter: M
Question
Monthly we download data for cities and states. Column A is City, Column B is State and Column C is Amount. The Cities is Column A can repeat and we can have the same city name is two different states. And from Month to Month we may have new cities in column A. What we want to do is come up with a total of the Amounts in Column C for each city in a State. And ultimately add the amounts up for a State total.
Ames Iowa $100
Chicago Illinois $50
Chicago Illinois $75
Duluth MN $500
Winona MN $200
Duluth GA $780
Want to get:
Ames Iowa $100
Chicago Illinois $125
Duluth MN $500
Winona MN $200
Duluth GA $780
Next month we could have the above cities plus another city in each state.
Any suggestion??
Explanation / Answer
You should do it using pivot table.
In the menu click on Insert > PivotTable > select data range > OK
Drag the field "City" from the PivotTable Field List box to the Row Labels box at the bottom, and then drag the "State"
Drag the "Amount" to Values box, click the drop down box of "Amount" > Value Fields Setting > Select "Sum"
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.