Total by location

[howardjo]

Morning,

I have attached a simple example of what I am trying to achieve.

In the example, I have a list of shops. Each shop shows its location and revenue.

What I need is a generic formula which will Sum all the revenue in the same location. I.e. Eastleigh = £200

However, in the actual document there maybe upto 100 different locations, and these may change on a daily basis.

Therefore, I am looking for Excel to identify all the locations which are the same and Sum the corresponding revenue. In a document which potentially has a changing number of locations.

Many thanks

Howardjo

[VBA Noob]

Maybe a dynamic pivot table.

See pivot for example and link to make it dynamic and a pivot demo link

http://www.contextures.com/xlPivot01.html

http://www.datapigtechnologies.com/f...es/pivot1.html

or take a look at sumproduct

http://www.contextures.com/xlPivot01.html

VBA Noob

[dominicb]

Good morning howardjo

Try using a pivot table. Do not use the Excel help system - it's utter rubbish and will just bamboozle you.

Highlight the range, go to Data > Pivot Table & Pivot Chart Report and jump right in and experiment for yourself.

HTH

DominicB

[howardjo]

Ok, will try this. Many thanks

[deadlyduck]

You could also check out the Data|Subtotals facility shown on the Data menu.