Create monthly headcount report

[maychui]

Hi friends,

I need urgent help in creating a formula for a monthly headcount report. I am able to find a formula suitable for me in an old thread. However, for some reasons, it does not exactly fit my case and I would need help on modifying the formula.
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alias[/TD]
[TD]Hidden Zero Date[/TD]
[TD]Join Date[/TD]
[TD]Last Date (YTD)[/TD]
[TD]Jan-15[/TD]
[TD]Feb-15[/TD]
[TD]Mar-15[/TD]
[TD]Apr-15[/TD]
[TD]May-15[/TD]
[TD]Jun-15[/TD]
[TD]Jul-15[/TD]
[TD]Aug-15[/TD]
[TD]Sep-15[/TD]
[TD]Oct-15[/TD]
[TD]Nov-15[/TD]
[TD]Dec-15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Anna[/TD]
[TD]0[/TD]
[TD]26-Sep-12[/TD]
[TD]21-Sep-15[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ben[/TD]
[TD]0[/TD]
[TD]20-Apr-15[/TD]
[TD]21-Sep-15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]=TODAY()[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=IF(MOD(MATCH(G$1,$D3:$F3),2)=1,"",1)[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]=IF(MOD(MATCH(H$1,$D3:$F3),2)=1,"",1)[/TD]
[/TR]
</tbody>[/TABLE]


The formula in E3, I found it in the old thread.
However e.g. for Ben, he joined on 20-Apr-15, and in my company we will count it as (1) no matter if the join date is in the beginning of the month, middle of the month or end of the month. In Ben's case, his headcount should be counted starting in April instead of May.

Please help! I would like to know how I should modify the formulas in order to calculate the correct headcount. Thank you in advance.

Regards,
May

 

[WhoCanDo]

Hi May,

is it possible that you have more understanding of what you want than what you have asked because the above doesn't make much sense. Are your formulas wrong since G$1 is "G" and E3 = a formula that has E3 in it therefore it's a circular reference that is not allowed in Excel.

 

[maychui]

Hi,
Thanks for your reply. I have made a mistake. Since I extracted this out of a confidential excel, I have edited out some columns and i forgot to change the formulas before pasting them here.
The formulas should be: [TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]E3[/TD]
[TD]=IF(MOD(MATCH(E$1,$B3:$D3),2)=1,"",1)[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]=IF(MOD(MATCH(E$1,$B3:$D3),2)=1,"",1)

does this make any sense to you now? [/TD]
[/TR]
</tbody>[/TABLE]

 

[WhoCanDo]

I think I know what your trying to do. If we take your example and assume that "A B C etc." are columns then "Alias" is line 1 etc. so maybe the following formula in E2 (to the right of Anna's YTD) may help?

=IF(AND(E$1>=$C2,E$1<=$D2),1,0)

 

[maychui]

Apologize for messing up with the columns and rows.
I have already updated the formulas but there is still a problem.
Please see below the result. I am not sure why it does not recognize Eva's headcount. It seems like it only recognizes the headcount when the join date is at the beginning of the month but not the middle of the month. For example, Eva's join date is on 20-April but it is not recognized in April but in May, however for Don, it works fine. Would it possibly be the problem of the formatting of dates?
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alias[/TD]
[TD]Join Date[/TD]
[TD]Last Date (YTD)[/TD]
[TD]Jan-15[/TD]
[TD]Feb-15[/TD]
[TD]Mar-15[/TD]
[TD]Apr-15[/TD]
[TD]May-15[/TD]
[TD]Jun-15[/TD]
[TD]Jul-15[/TD]
[TD]Aug-15[/TD]
[TD]Sep-15[/TD]
[TD]Oct-15[/TD]
[TD]Nov-15[/TD]
[TD]Dec-15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Don[/TD]
[TD]1-Apr-15[/TD]
[TD]21-Sep-15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Eva[/TD]
[TD]20-Apr-15[/TD]
[TD]21-Sep-15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[WhoCanDo]

Then the formula needs to make each person's starting date the 1st so -

=IF(AND(D$1>=DATE(YEAR($B2),MONTH($B2),1),D$1<=$C2),1,0)

 

[maychui]

Thanks loads. I have added in something to part of the termination date of the formula and it works perfect now.