Count active cases between two dates

[VB_75]

Hi Excel Forum Members,

I'm searching for an elegant and effective solution for the following challenge:

My data set contains information when a case was reported (Date reported) and also when a case was closed (Date closed). If the case is not closed yet, the cell "Date closed" remains empty. Each case is assigned to a handler (Person 1 - 5).

I know how to insert a quick Pivot Table to analyze the current status of how many open cases does each loss handler have today.

What I'm trying to achieve is a bar chart to show the development of open files at defined dates. In my file, I have set monthly dates and counted the amount of open files at that day manually... I'm trying to find a way to analyze:

On each defined date, please count the number of files per case handler that were opened before the defined date and not yet closed on that date. With that analysis, I want to visualize the workload per case handler over time.

I hope I described my goal well enough, in case of any questions please let me know.

Looking foreward to your comments and thanks for your support,

VB_75

[Estevaoba]

Welcome to the forum.
Not sure I get it right.
I made a list of unique handlers in column Q so as to use it in a data validation in H1.
Then I use this formula in H4, copied across:

Please Login or Register  to view this content.

Good luck!

[Gregb11]

Cell H3 try:
=SUMPRODUCT((Tabelle1[Date reported]<=H$2)*((Tabelle1[Date closed]>H$2)+(Tabelle1[Date closed]="")))

You state, "please count the number of files per case handler that were opened before the defined date", however, I'm guessing you mean, "were opened before OR ON the defined date. If you want it truly BEFORE the defined date, remove the "equal" sign before the first "H$2".

Also, for June 1, 2021, you show 10. I'm showing 8 because your data shows that there were only 8 open on June 1.

[kvsrinivasamurthy]

In P3 copied down

Please Login or Register  to view this content.

In Q3 then copied to full range

Please Login or Register  to view this content.

[VB_75]

Thank you all for your prompt feedback, this is amazing!

I will have a look and hope it is ok if I have any follow-up questions.

Cheers

[VB_75]

Hi Estevaoba,

Love the approach how you broke it down to the assigned handlers!

There are some issues with the results, and I believe it is because the formula ties it to the Status as open. For example, the first line (ID 805643) shows status "closed" which is the file's status today; however, it was open from 1-Jan until 27-Mar and assigned to "Person 1", so it should count as 1 for Person 1 for the Jan, Feb and Mar count. Since it was then closed on 27-Mar, this file would count as 0 from the Apr analysis onwards.

Many thanks!

[Estevaoba]

You're welcome.
OK, my bad. I think I got it now, by adding two COUNTIFFS:

Please Login or Register  to view this content.

Please give it a shot.

[kvsrinivasamurthy]

Revised formula in S3, copied to full range

Please Login or Register  to view this content.

For P3 then copied down

Please Login or Register  to view this content.