+ Reply to Thread
Results 1 to 3 of 3

Count of historical ticket by month based upon an open and closed date

  1. #1
    Registered User
    Join Date
    05-11-2021
    Location
    Kentucky
    MS-Off Ver
    2016
    Posts
    1

    Count of historical ticket by month based upon an open and closed date

    I am in need of assistance with coming up with a function to count a list of ticket from 1/1/2018 - Current. Some are closed and some are still open.

    My Goal:
    1. Count of tickets opened in that month. This was easily solved with a count if statement for greater than or equal to the 1st of the month but less than the start of the following month.
    2. Count of tickets opened in that month. Easily solved for using the same count if statement as above but referencing the closure date.
    3. Difference between open and closed, easily solved with Open minus closed.
    4. Count of tickets that remained open for more than one month. This is where I need help.

    Example:
    If the ticket was open in January and closed in March I want it to count as open for all three months.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,817

    Re: Count of historical ticket by month based upon an open and closed date

    Hello Mr. TDP and Welcome to Excel Forum.
    It would seem to me that the formula for cells E2 and down would be: =SUM(D$2:D2)
    The reason being that the number of tickets open would be the sum of the balance of the prior month and the current month.
    So for example there were 218 tickets open at the end of Jan. 2018. At the end of Feb. 2018 an additional 167 tickets were left open so at that point there would be 385 total open tickets.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Count of historical ticket by month based upon an open and closed date

    Cell E2=B2-COUNTIFS(H:H,">="&A2,H:H,"<"&A3,I:I,"<"&A3)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 18
    Last Post: 10-11-2023, 04:58 AM
  2. Replies: 1
    Last Post: 02-28-2020, 04:15 AM
  3. [SOLVED] If statement to mark cell Open or Closed based on date
    By ap1591 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2018, 05:17 PM
  4. [SOLVED] Day count open versus closed
    By estrangedred in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-12-2017, 07:34 AM
  5. Replies: 4
    Last Post: 10-21-2016, 09:52 AM
  6. Excel 2007 : Ticket Date Count
    By 4sacobb in forum Excel General
    Replies: 1
    Last Post: 07-25-2011, 04:15 PM
  7. Rolling 12 month historical date issue?
    By bex9mm in forum Excel General
    Replies: 1
    Last Post: 11-17-2009, 01:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1