KDavidP1987
Board Regular
- Joined
- Mar 6, 2018
- Messages
- 51
Hello Gentlemen and Scholars,
Having a bit of a problem coming up with a COUNTIFS formula to get a task done at work, could use some of your wisdom.
Context:
I have a spreadsheet of ticket data from a ticketing system, and a 2K+ list of roles the agents handle. I have to count the number of occurrences where role changes are being requested, monthly and then weekly.
The role information is stored in a field (cell) called description, which the agents and users enter notes into. It's the only location I can pull the data from, unfortunately. Each ticket may contain multiple role change requests within the description, so I have to capture all of these.
I tested a simple COUNTIFS formula first, to ensure I could pull the necessary data, and it worked.
However, I am having trouble with apply time into the formula using a COUNTIFS
I built two matrix's (worksheets), of Role information (rows) and Date information (Column headings). Months of the year in one, Weeks of the year in the other, using the last day of each week. I was able to calculate the weekly data using the following formula:
*though, I am having some additional issues with dragging over the formula across the weeks, because of relative cell referencing in the formatted table ranges, so figuring that part out myself*
However, I cannot figure out how to calculate the monthly Data.
Question:
How can I count the data for tickets worked within a given month, where the date submitted from the YTD_Data worksheet (containing ticket information) falls within the month on my column header of the matrix (January, February, March, etc...) and keeping the part where the description text matches the role in column A:A (the [@ROLE] column)
Sincerely,
Having a bit of a problem coming up with a COUNTIFS formula to get a task done at work, could use some of your wisdom.
Context:
I have a spreadsheet of ticket data from a ticketing system, and a 2K+ list of roles the agents handle. I have to count the number of occurrences where role changes are being requested, monthly and then weekly.
The role information is stored in a field (cell) called description, which the agents and users enter notes into. It's the only location I can pull the data from, unfortunately. Each ticket may contain multiple role change requests within the description, so I have to capture all of these.
I tested a simple COUNTIFS formula first, to ensure I could pull the necessary data, and it worked.
Code:
=COUNTIF('Data_YTD'!L:L,"*"& [@ROLE] &"*")
However, I am having trouble with apply time into the formula using a COUNTIFS
I built two matrix's (worksheets), of Role information (rows) and Date information (Column headings). Months of the year in one, Weeks of the year in the other, using the last day of each week. I was able to calculate the weekly data using the following formula:
*though, I am having some additional issues with dragging over the formula across the weeks, because of relative cell referencing in the formatted table ranges, so figuring that part out myself*
Code:
=COUNTIFS(YTD_Data[Date Submitted],"<=" & Table13[[#Headers],[1/6/2018]],YTD_Data[Date Submitted],">=" & Table13[[#Headers],[1/6/2018]] & "-6",YTD_Data[Descriptions],"*"& [@ROLE] &"*")
However, I cannot figure out how to calculate the monthly Data.
Question:
How can I count the data for tickets worked within a given month, where the date submitted from the YTD_Data worksheet (containing ticket information) falls within the month on my column header of the matrix (January, February, March, etc...) and keeping the part where the description text matches the role in column A:A (the [@ROLE] column)
Sincerely,