Counting users between start and end dates

Question

I am trying to determine the number of active participants for each month.

I have (A) User ID (B)Project Entry Date (C)End Date

Now, the end date either has an actual date or "blank". Blank indicates that they are still active.

Essentially I need a formula or other strategy to capture something like:

Jan (count if (a) project start date: before or on 1/31/2020 AND (b) end date is blank OR on/before 1/31/2020)

And I need to do this for each month.

Can anyone help please?

hansvogelaar · Answer

apittman1565&nbsp;
Don't you mean "(a) project start date: before or on 1/31/2020 AND (b) end date is blank OR on/after 1/1/2020)"? With your description, someone starting on 1/1/2020 and ending on 2/1/2020 would not be counted because the end date is not on/before 1/31/2020.
I'd do it like this:

The formula in G3 is an array formula confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT(($B$2:$B$9&lt;=EOMONTH(F2,0))*(IF($C$2:$C$9="",F2,$C$2:$C$9)&gt;=F2))
This can be filled down (after adjusting the ranges.
You can format F2 and below to display only the month or month and year.
&nbsp;