Pivot table - counting data in columns with same heading

peteh

New Member
Joined
Oct 26, 2010
Messages
21
Hi,

I have a data table that tracks errors being made and there are 4 column headings called 'rejection cause (1-4)' so that multiple errors can be recorded against the same work item. These columns are populated with text info (from a drop down list).

I'm trying to build a pivot table that tracks the trend of error types made on a weekly basis which I'm fine with, my problem is that I don't know how to combine the data in the 4 columns so that they are treated as one in a pivot table rather than separated out - is this possible?

Thanks,

Pete
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi

You can add a new column with formulas to the source table or create a calculated field on the pivot table, as shown below:


ZzhZDOu.jpg
 
Upvote 0
Thanks for the reply Worf but unfortunately that doesn't fix my issue as my data is text, not numeric. For example, may data table may have 10 lines of info for April. Line one may have RC1 as "calc error" and on the same row, RC2 as "missing info". The next 2 rows in the data table may have RC1 as "calc error" (no info in RC 2, 3 or 4) and the next 7 rows may have RC1 as "missing info", RC2 as "spelling error" and RC3 as "insert ommission"

I'd like the pivot to tell that in April I had the following errors:
- 3 calc errors
- 8 missing info
- 7 spelling errors
- 7 insert ommissions


Hopefully the above makes sense??

Thanks
 
Upvote 0
o Here is one way to do it; tell me if your source data is not arranged like this.
o Do you need my test workbook?

a84VzPt.jpg



nnObNJg.jpg
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top