Pivot Table Count

You can't do that with a pivot table. You can group and bundle things in row or column headers, but anything in the data grid is going to come out as a number.

You can use Match / Index to look up text information. If the rows and columns are "static" you can set this up. If the data is dynamic then you will have to use VBA to make the column and row headers and fill in the formulas.

I put the code in an Excel table. I see by your profile that you have Excel 2003 that does not support tables, but the XLSX file you attached indicates you have at least 2007 which does support tables.

Tables don't confer a great advantage here as they do in most other programs but is does make it easier to track the columns.

I added a helper column to the table. What we want to track depends on both the Ref and the code so I made a composite that has the formula: =SUBSTITUTE([@Ref]&":"&[@Code],"#",""). This is the Ref + the Code with a delimiter in between (for easier reading). I had to wrap the SUBSTITUTE around the whole thing because P100 and P002 codes have trailing # but the column headers on the display do not.

Then we move over to Sheet 1 where we take advantage of INDEX and MATCH: =IFERROR(INDEX(Table_Codes,MATCH($A2&":"&B$1,Table_Codes[Composite],0),2),"")

I make a composite out of the row and column headers using partial absolute addressing (keep the column static for the Ref and the row static for the code). I use the composite to find the matching row in the table and we want the second column from the table that is on this row. There are a lot of "misses" that would normally evaluate to #N/A, so I "silenced" them with IFERROR.

Hi Gerald,

Would this answer be ok?

PT Count instead of Text.xlsx