Hello!
As the thread title says, I'm looking for some help extracting and counting/averaging data from a range. I don't know if it can done with a formula or not, but the caveat here is that I cannot use VBA or pivot tables.
Our company week is Sunday-Saturday. Every Sunday I run reports for the previous week of agent productivity. There are 4 sheets I send to management: Previous Week, Month-to-Date, Quarter-to-Date and Year-to-Date.
There is one report I run for Calls, one for Call Survey Results, One for Emails and one for Chats. I have to run each of these 4 reports 4 different times to get the data for each of the 4 sheets mentioned above.
I have a master file that I use to create each of the 4 final sheets. I have to paste in each of the 4 reports to do the calculations for each of the four sheets. Not super time consuming, but certainly not efficient as I am essentially do the same thing 4 times. Assuming I don't make any mistakes, this takes a total of about 2.5 hours.
What I would like to do, is run the Year-To-Date reports, plug that into a master file, and have each of the 4 sheets do the necessary calculations. I have already created the master file and have everything working for the Year-to-Date sheet. What I cannot figure out, is how to extract the Week-to-Date, Month-To-Date and Quarter-to-Date data.
Here is a screen shot of a portion of my company's fiscal calendar:
Columns A and B are obvious, C = Fiscal Week, D = Fiscal Month, E = Fiscal Quarter
Here is a screenshot of part of the Year-to-Date data, in tabulated form:
Here is the Quarter-to-Date sheet where the data will be calculated and displayed:
So, for this sheet, what I need is for C4 to calculate for Name 1 in Column A the number of calls from Column E of the Year-To-Date data, but only for the dates from the Fiscal Calendar in Column B that have a 4 in Column E.
Hopefully, that all makes sense. Any help is greatly appreciated.
As the thread title says, I'm looking for some help extracting and counting/averaging data from a range. I don't know if it can done with a formula or not, but the caveat here is that I cannot use VBA or pivot tables.
Our company week is Sunday-Saturday. Every Sunday I run reports for the previous week of agent productivity. There are 4 sheets I send to management: Previous Week, Month-to-Date, Quarter-to-Date and Year-to-Date.
There is one report I run for Calls, one for Call Survey Results, One for Emails and one for Chats. I have to run each of these 4 reports 4 different times to get the data for each of the 4 sheets mentioned above.
I have a master file that I use to create each of the 4 final sheets. I have to paste in each of the 4 reports to do the calculations for each of the four sheets. Not super time consuming, but certainly not efficient as I am essentially do the same thing 4 times. Assuming I don't make any mistakes, this takes a total of about 2.5 hours.
What I would like to do, is run the Year-To-Date reports, plug that into a master file, and have each of the 4 sheets do the necessary calculations. I have already created the master file and have everything working for the Year-to-Date sheet. What I cannot figure out, is how to extract the Week-to-Date, Month-To-Date and Quarter-to-Date data.
Here is a screen shot of a portion of my company's fiscal calendar:
Columns A and B are obvious, C = Fiscal Week, D = Fiscal Month, E = Fiscal Quarter
Here is a screenshot of part of the Year-to-Date data, in tabulated form:
Here is the Quarter-to-Date sheet where the data will be calculated and displayed:
So, for this sheet, what I need is for C4 to calculate for Name 1 in Column A the number of calls from Column E of the Year-To-Date data, but only for the dates from the Fiscal Calendar in Column B that have a 4 in Column E.
Hopefully, that all makes sense. Any help is greatly appreciated.
