COUNTIFS, with date in listed month, and where text shows up

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.

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. :eek:

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) :confused:


Sincerely,
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
After some finagling with the formulas, I decided to just resolve the matter manually within each column. I used the following formula and then copied it over and changed the date for each of the 12 columns

Code:
=COUNTIFS(YTD_Data[Date Submitted],">=" & "01/01/2018", YTD_Data[Date Submitted],"<" & "02/01/2018",YTD_Data[Descriptions],"*"& [@ROLE] &"*")
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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