Pivot Count with WildCards, possible

[KDavidP1987]

Hello,

I have a question regarding a viable solution for a request I received at work. I am doing reports on ticket system data for a call center, and one of the managers wants a count of the tickets where a list of key terms (unique roles) are mentioned in a field (Description). He wants this list separated out by month, with totals for each year, cover data from the previous year and current year both. IN addition, he wants % of Column and % of Row fields, like pivot tables include.

So, I am left in a conundrum. Getting a count of these "keywords" (job roles) is easy using a COUNTIF() with wildcards, and adding the monthly layer is easily accomplished using COUNTIFS() with criteria which specifies the tickets per month. However, is there a way I can do this in pivot table or some other fashion which allows me to generate the % of row and % of columns results automatically? The current method is a hardcoded table with each column representing the month (or year total), and it is process heavy (freezes the excel workbook)

For reference: (current formula)

=COUNTIFS(DS_2018TicketData[Description],"*"&[@Role]&"*",DS_2018TicketData[Date Submitted],">="&D$1,DS_2018TicketData[Date Submitted],"<="&EOMONTH(F$1,0))

Also for reference, The roles it's searching for through the text fields look something like this (but there are 100's). The text boxes theyre searching through are potentially massive in content, as they contain all transcriptions which occur through ticket handling

[TABLE="width: 500"]
<tbody>[TR]
[TD]ZS_CPT_AA_MANAGER
[/TD]
[/TR]
[TR]
[TD]ZS_CPT_BB_MANAGER[/TD]
[/TR]
[TR]
[TD]ZS_CPT_CC_SUPERVISOR[/TD]
[/TR]
[TR]
[TD]ZS_CPT_NW_INTEGRATIONMGR[/TD]
[/TR]
</tbody>[/TABLE]


Sincerely,