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

# 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.

sample-sheet-8-27.xlsx
0
Edward Pamias
• 3
• 3
1 Solution

Commented:
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

Unfortunately I cant change it to a table. Would the offset function work as an alternative?
0

Commented:
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

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

Commented:
OK, you should be able to us offset(\$E\$13,1,0,counta(\$C:\$C)-1,1) then.
0

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.