Counting users between start and end dates

[apittman1565]

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?

[#2]

@apittman1565 

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<=EOMONTH(F2,0))*(IF($C$2:$C$9="",F2,$C$2:$C$9)>=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.

 

[#3]

@apittman1565 

You can use simple pivot table to count the users:

[#4]

Nice job, Hank :)

[#5]

Yes, that's what I actually meant. Thank you for catching that. And thank you for the formula! @Hans Vogelaar