Hello Every one,
I have got a requirement where we have to show number of open tickets every day. Each support ticket has unique id and an open date and closed date(if the ticket is closed). Attached is the sample excel which contains sample data-set we have and an expected output. Comment section provides reason of count in expected output.
Please suggest if more information is required and Thanks in advance
=COUNTIF($D$3:$D$14,"<="&G3)-COUNTIF($E$3:$E$14,"<="&G3)
count the number of tickets open on that day or earlier and subtract the number of closed tickets on that day or earlier
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Good Afternoon Ranjit.
Insert a new Row 3. It can be hidden if you want.
Formula for H4 then Fill down =COUNTIF(D4:D15,G4)-COUNTIF(E4:E15,"<=" &G4)+H3.
That works for now. However it is not Dynamic.
I will now work to make it Dynamic.
etafs formula is better than mine. So I have modified your table using that.
1. I selected you Data C2:E4 and converted it to a table using Insert then table.
2. The formula for H3 becomes =COUNTIF(Table1[Ticket Created Date],"<=" &G3)-COUNTIF(Table1[Ticket Closed Date],"<=" &G3)
4. Finally I have created a small but powerful macro for you.
Open the Attachment and select C13. Did you notice what happened? Select C14,
Now Select E13. Select E14
Right Click on the sheet name at the bottom of excel and select view code to see it
Please Login or Register to view this content.
Last edited by mehmetcik; 02-23-2020 at 10:56 AM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Thanks a ton etaf and appreciate your super quick response....!!
Never thought it was going to be so quick and accurate.
you are welcome
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks