we have a couple of hundred customers each are are charged a different fee every month. Each month is on its own sheet.
How can I calculate the annual total fee per customer? -They are not in any order and there are often new customers in the middle of the year.
By far, the easiest way to do this is a pivot table.
- Open your spreadsheet and hit ALT>D>P to start the pivot table wizard.
- On the first page of the wizard, select 'Multiple consolidation ranges' and click next.
- On the next screen, select 'I will create the page fields' and click next.
- On the next screen, press the little icon with the red arrow, navigate to your first monthly tab, and select the whole table that has your values, including the headers, names, and fee values. If you want to set this up before the values are put in (ie at the beginning of the year), just highlight the entire columns - for example Sheet1!$A:$B if you have only 2 columns of data - and that way later you can just refresh the pivot table results after new data has been entered. Once you have the range selected, click add, and repeat the process for each monthly tab, then click next once you finish the last one.
- On the next screen, select 'New worksheet' and click finish.
The wizard will then generate a pivot table, and a pivot report showing the results. Each row in the pivot table will show one customer name, alphabetized, with no duplicates. When the report is first generated, it'll show a count of how many values there are for each name. To switch that to a sum of actual values, look at the bottom right corner of your screen, in the Values section of the PivotTable Field List window. Click the little arrow next to the Count of Value field, select Value Field Settings, and switch from Count to Sum in the window that pops up. The report will now show the final result.
If you add or remove data from the source ranges (ie your monthly tabs) later, you can always click into any cell in the report and hit ALT>J>T>F>A to refresh the pivot table.
ETA: I did that in Excel 2007, but it should work similarly in all newer versions as well at the very least.
Topic Wiki
