Question:
=IF((COUNTIFS(Data!F:F,35,Data!H:H,E2))<=0,"Future",(COUNTIFS(Data!F:F,35,Data!H:H,E2)))
The above doesn't work on filtered data. I've scowered the internet and failed to find a solution after hours of trying. I managed to find a solution for a single count on a different column:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!G:G,ROW(Data!G:G)-MIN(ROW(Data!G:G)),,1)),ISNUMBER(SEARCH(A3,Data!G:G))+0)
I cannot figure out how to manipulate this in order to find the count of rows with both values of 35 and E2. Any ideas?
Back story:
I have a system which is used for a service desk. This has tickets on it which have recorded data on them.
Worksheet 1 (Data):
I have exported this data into a spreadsheet to create trend charts on. The data collected is:
ID, Tag, Department, Month Created, Month Resolved, Week Created and Week Resolved.
I have then created another column which works out the difference in weeks (i.e. how many weeks it takes to resolve a ticket) and applied a filter to these columns.
Worksheet 2 (Count Data):
I have a table that counts the Amount of tickets created and resolved each week. Also I have another table that counts how many were resolved by how many weeks it took to be resolved, for example there are 235 tickets, that were created in week 35 and took less then 1 week to be resolved.
Worksheet 3 (Charts):
I have two charts that show the resolution/creation trends over the weeks and the counts of each week by how long the tickets took to resolve.
Problem:
Quite a simple spreadsheet however when I filter the Data's department to only show specific departments the charts remain unchanged. It turns out this is because the COUNTIF and COUNTIFS functions count regardless of filters. How do I get around the COUNTIFS issue?
