I'm looking for a way to calculate open tickets (Backlog)Open - Closed - Backlog month end

Hello I'm struggling with calculating month-end backlog tickets.
Attached I have sample data in an Excel file.

I'm looking for a way to calculate open tickets (Backlog) at the end of the month

Example: Is ticket is opened on January 21st and closed on February 2nd, this should count as a backlog in the month end of January
If another ticket is created on the same day but closed on January 30th, I should not be counted in the backlog.
It can also happen that a ticket is open for several months (so open on January 21st but closed in May, then this ticket backlog should appear in January, February, March, and April as backlog)

I'm hoping for some help.

Thanks.
Emoes

Add

Please Login or Register  to view this content.

to H2 then copy down.
This says count all start dates greater than or equal to date in G2 AND less than g2 date plus 1 month AND Closed Date greater than G2 date plus one month
Might need to make it greater than or equal to

Please Login or Register  to view this content.

Can you please try to make your titles a little more explicit in future instead of just a bunch of key words? Thanks.

This:

I'm looking for a way to calculate open tickets (Backlog) at the end of the month

Thread titles should be Google AND human friendly, please, not a guessing game.

By the way, tags are for metadata.

Thank you.

I reworked the formula

Please Login or Register  to view this content.

I checked it by deleting all the data except 12-18. The problem: it is returning 3 extra Backlogs - I am guessing because it is importing the time stamp with the date.

So I added column D & E with the formula:

Please Login or Register  to view this content.

etc...
and changed

Please Login or Register  to view this content.

...The attached file “Backlog V1.2.xlsx” contains “live” data exported on December 31st, 2020...

I don't see a file Backlog V1.2 just Backlog attached to post #1 and Backlog V1.1 attached to post #9.

The following placed in cell H2 and copied down yields 280 for Jan-21 (cell H15):

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread