Forum Discussion
Counting users between start and end dates
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 indicat...
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.
Yes, that's what I actually meant. Thank you for catching that. And thank you for the formula! HansVogelaar