Quote Originally Posted by dflak View Post
What is the issue with using a pivot table?

Also, are the reports "fixed" meaning that you have region level and state level? And are the regions, states and counties fixed meaning that only the dollar amounts change for them?

If the reports are relatively "hard coded" (the regions and states don't change a lot) then you can use SUMIFS as shown in the attached file. The attached file uses Excel tables and SUMIFS which are not available in *.xls files. You have Excel 2010, so you have tables and SUMIFS.

What I recommend is using this file. Clear out the table data (highlight rows and right click and select Delete -> Table Rows) and copy and paste the raw data in.

Here is more information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

How do you get your source data? It may be possible to read the source data directly into the Excel table and skip the copy / paste step.
DFLAK has given a *great* answer for you. I'd just add a couple small changes to verify the fixed/hard-coded regions - use some TOTALS. I'd add a cell to calculate the total for Col D (Grand Total), and a total for each of the smaller tables (SubTotals). Use another cell to subtract one of the subtotals from the grand total to ensure a new/different region was not inadvertently or intentionally added to the table but were not included in either subtotal.

For this reason, I'd highly recommend a PIVOT TABLE to gather your info, as a Pivot Table will automatically add all regions.

FYI - the data from a Pivot Table can be copied, then PASTE-SPECIAL VALUES to another place if you absolutely need the info elsewhere.

Hope this helps!