Excel Formula - Items Open Within a Month

[CM1993]

Hi All,

Sorry I did try searching for the answer but couldn't find one specifically for my query.
I need to do some historical statics on incidents that have been open during the months of the year, the issue is that I can't seem to work out the correct formula or can't do it via pivot table

E.g. you could have an incident open in January, but then it isn't closed until March
Within that time you have a new item opened in February, that is also closed in February
Using that example, I would be able to know that there was 1 item active within the month of Jan / 2 active in the month of Feb / 1 active in the month of march

Hopefully that makes sense.. Bit hard to explain.

I've attached the dummy data as a reference, would there be a good formula to work this out?

Thanks.

[FlameRetired]

Hello CM1993. Welcome to the forum.

Instructions are not very clear. I took a guess and put those months (current year) in a header row. Then this formula in the row beneath.

Please Login or Register  to view this content.

Is this what you wanted?

	E	F	G
1	1/1/2021	2/1/2021	3/1/2021
2	3	0	3

[CM1993]

Hi Dave,

Thanks for replying so quickly.
I tried to do it myself based on the below formula, but doesn't seem to be working.

Please Login or Register  to view this content.

And I've attached an updated to excel to show what I'm trying to do to.
If I was to do it manually, there would have been:

Jan-18 : 16 Items active that month
Feb-18 : 18 Items active that month (6 New in Feb, but 4 were closed in Jan)
Mar-18 : 20 Items active that month (4 new in March, but 2 were closed in Feb)


again, still not sure if I'm explaining that right!

[JohnTopley]

Try

=COUNTIFS($C2:$C250,"<=" &EOMONTH(F$1,0),$D2:$D250,">=" &MIN(F$1, EOMONTH(F$1,0)))

See attached

[CM1993]

That was exactly what I was looking for!

Thank you so much for the help!!

[JohnTopley]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.