Convert some columns to pivot table rows?

I'm having an issue figuring out how to display some of my data while retaining the ability to filter on other columns.

My data is setup as below:

Date	Col1	Col2	Col3	Col4
1/1/2015		1		2
1/1/2015	1		1	2
1/2/2015	1	1	1	3
1/2/2015	1	1		2
1/3/2015			1	3

I would like to be able to display the data in a pivot table, with Col1-Col4 being the rows and then the count and sum of each column being the values, so I could then also filter the data based on the Date column, or any of the other columns in the spread sheet.

Any help would be greatly appreciated!

Hi,

I have created a Pivot table in the attached file based on the description provided by you, see if it suits your requirement!

Thanks for putting that together cbatrody, unfortunately it's not quite what I am looking for. My data is going to have anywhere from 4 to 20 columns that will need to be on the pivot table, and I'd like to make it as easy to see the data as possible.

I envision the resulting pivot table looking like this:

Column_Name	Count	Sum
Col1
Col2
Col3
Col4
Grand Total

With the ability to then add filters to the pivot table based on other columns in the worksheet.

I currently manipulate the data with macros to then create a pivot table as presented in the table above, but to do this I have to change the format of the data, eliminating the connection to other columns, thus eliminating the ability to filter.

I'm not sure if what I am looking for is possible without manipulating the data at the start.

Thanks for that oeldere, the resulting pivot table is what I am looking for, but the method of converting the data unfortunately won't work for what I am doing. There will potentially be as many as 20 columns to handle, and breaking each row down to 20 rows would mean it wouldn't work on any spreadsheet with 50,000 or more rows. Which will be fairly common for my data sets.

It looks like I'll probably have to move the data to Access to be able to do what I'd like to do efficiently. Thanks for the help all!

I did test the macro, Excel stopped responding for a while and eventually spit out a 1004 error on the macro, due to range in _Worksheet.

The issue is that Excel can only handle a little over a million rows, and when dealing with 20 columns and 100,000 rows, the macro errors out when it hits Excel's limit.