Counting users between start and end dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2099890%22%20slang%3D%22en-US%22%3ECounting%20users%20between%20start%20and%20end%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2099890%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20determine%20the%20number%20of%20active%20participants%20for%20each%20month.%3C%2FP%3E%3CP%3EI%20have%20(A)%20User%20ID%20(B)Project%20Entry%20Date%20(C)End%20Date%3C%2FP%3E%3CP%3ENow%2C%20the%20end%20date%20either%20has%20an%20actual%20date%20or%20%22blank%22.%20Blank%20indicates%20that%20they%20are%20still%20active.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%20I%20need%20a%20formula%20or%20other%20strategy%20to%20capture%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJan%20(count%20if%20(a)%20project%20start%20date%3A%20before%20or%20on%201%2F31%2F2020%20AND%20(b)%20end%20date%20is%20blank%20OR%20on%2Fbefore%201%2F31%2F2020)%3C%2FP%3E%3CP%3EAnd%20I%20need%20to%20do%20this%20for%20each%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2099890%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2101460%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20users%20between%20start%20and%20end%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2101460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F943877%22%20target%3D%22_blank%22%3E%40apittman1565%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDon't%20you%20mean%20%22(a)%20project%20start%20date%3A%20before%20or%20on%201%2F31%2F2020%20AND%20(b)%20end%20date%20is%20blank%20OR%20on%2F%3CSTRONG%3Eafter%3C%2FSTRONG%3E%20%3CSTRONG%3E1%2F1%2F2020%3C%2FSTRONG%3E)%22%3F%20With%20your%20description%2C%20someone%20starting%20on%201%2F1%2F2020%20and%20ending%20on%202%2F1%2F2020%20would%20not%20be%20counted%20because%20the%20end%20date%20is%20not%20on%2Fbefore%201%2F31%2F2020.%3C%2FP%3E%0A%3CP%3EI'd%20do%20it%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0048.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249781i6789813ED35B71ED%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0048.png%22%20alt%3D%22S0048.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20G3%20is%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24B%242%3A%24B%249%26lt%3B%3DEOMONTH(F2%2C0))*(IF(%24C%242%3A%24C%249%3D%22%22%2CF2%2C%24C%242%3A%24C%249)%26gt%3B%3DF2))%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down%20(after%20adjusting%20the%20ranges.%3C%2FP%3E%0A%3CP%3EYou%20can%20format%20F2%20and%20below%20to%20display%20only%20the%20month%20or%20month%20and%20year.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

4 Replies

@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:

S0048.png

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.

 

@apittman1565 

You can use simple pivot table to count the users:

 

 

Nice job, Hank

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