Pivot table - counting data in columns with same heading

[peteh]

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

 

[Worf]

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:

 

[peteh]

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

 

[Worf]

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?