• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

Count the number of days formula and average numbers formula

If you look in my sheet, there is a field called dispatch. In that column are dates I need to count the days from the current date to that date listed in the column and populate the fields below. Keep in mind the number of rows changes daily so I would need this to work dynamically.

I need to know how many :

Tickets open 1-2 Days            
Tickets open 3-5 Days            
Tickets open 6 -10 Days            
Tickets 11+ Days      

I also need to average out DISPATCH DELAY column.

Thanks in advance for your assistance!
sample-sheet-8-27.xlsx
0
Edward Pamias
Asked:
Edward Pamias
  • 3
  • 3
1 Solution
 
nutschCommented:
To make it dynamic, the easiest is to turn your range into a table. Then using countif and countifs is easy. See attached file.

Thomas
Copy-of-sample-sheet-8-27.xlsx
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Unfortunately I cant change it to a table. Would the offset function work as an alternative?
0
 
nutschCommented:
Yes it would, it's just more of a pain to manage. Your open date column is would be offset($C$13,1,0,counta($C:$C)-1,1) instead of (tbDispatch[OPEN DATE]

Thomas
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thanks Thomas... it's not going by open date... I am counting from today's date till the dates in the Dispatch column and then getting the number of tickets for each of the fields.
0
 
nutschCommented:
OK, you should be able to us offset($E$13,1,0,counta($C:$C)-1,1) then.
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thanks Thomas .. this should get me on the right path if any problems I will post another question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now