Aggregation at different level

Hi Guys, I am new to this forum, and not very good in Excel Functions.

I have a requirement where i have to show Aggregation of data at different levels. Following data i attached.

Region State County Revenue
North Montana Madison $10,000
North Minnesota Pine $20,000
North Minnesota Benton $25,000
South Texas Collin $50,000
South Texas Smith $40,000
South Alabama Blount $15,000
East New York Clinton $60,000
East New Jersey Essex $40,000
East New Jersey Camden $35,000
West California Fresno $55,000
West California Imperial $50,000
West Oregon Wasco $30,000

- i have to show only Region with Revenue,and this is without Pivoting in the excel, because i will be taking this data and making as an input to another.
Region Revenue
North $55,000
South $105,000
East $135,000
West $135,000

- Same thing with one more column, with lower granularity

Region State Revenue
North Montana $30,000
North Minnesota $25,000
South Texas $90,000
South Alabama $15,000
East New York $60,000
East New Jersey $75,000
West California $105,000
West Oregon $30,000

If we can capture these scenario at the source data, that's great, or if we have to map it to another place, we can do that as well. But in any case it has be derived using functions.

Thanks so much.

Originally Posted by dflak

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!