Grouping/Subtotaling Data based on Criteria

[skashat]

Hello,

I have a set of data with about 8 different columns, and about 1,000 rows of raw data. The process to summarize this data right now, is to copy it all and paste it into an Access database. Access will then summarize all the data and spit it out back into Excel. I am wondering if I can do this in Excel alone.

The data on the left is a (simple) example of the raw data. The data on the right is how I want it to be.


-- removed inline image ---


The data on the right basically summarizes it based on Group. However, if I want to, I should be able to summarize it by Dept and/or Date. In this example, if I summarized it by Date alone, it should give me 33,000 total in one line.


I don't want to have to use subtotals or grouping. Is there a way to get what I want with Excel?

 

[CWatts]

It's probably doable but it would be a relatively hefty macro, or you would have to do a lot of manual calculations using SUMIFS worksheet functions.

The easiest way I'd suggest would be a Pivot Table. I don't know if you wrote it off because the format wouldn't be exactly like what you have or if you've considered it yet, but it's the easiest method for manipulating and summarizing large data sets.

I don't know how to meet your specifications exactly or if the pivot table grouping is specifically what you want to avoid, but figured I'd throw this idea into the hat for consideration in case it hadn't been yet.

 

[skashat]

Thank you for the help and response.

I figured it would be doable through concatenations and sumifs, but I just wanted to see if basically there was already a built-in function in Excel that could get me what I want.

I think your way of using a pivot table would probably be the best route.

Thank you again for the help.

 

[Akashwani]

Hi,
Welcome to MrExcel

Sorry to jump in here and I'm probably totally misunderstanding your question, but, could you not do part of it (Date,Dept,Group) with Advanced Filter, then use Sumifs for Amount? That is what I did with your sample data....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Date	Dept	Group	Amount	*	*	*	*	*	*
2	11/04/2011	4	1	500	*	*	Date	Dept	Group	Amount
3	11/04/2011	4	2	1000	*	*	11/04/2011	4	1	1000
4	11/04/2011	5	1	5000	*	*	11/04/2011	4	2	2000
5	11/04/2011	5	2	10000	*	*	11/04/2011	5	1	10000
6	11/04/2011	4	1	500	*	*	11/04/2011	5	2	20000
7	11/04/2011	4	2	1000	*	*	*	*	*	33000
8	11/04/2011	5	1	5000	*	*	*	*	*	*
9	11/04/2011	5	2	10000	*	*	*	*	*	*
10	*	*	*	33000	*	*	*	*	*	*
Sheet2

Just a thought.

Ak