Formula to count leaves in a month

[vinod9111]

Hi All,

Below is the data of the staff who has taken leave on the below dates. I need a formula which can cull out month wise , staff wise leave taken.

Data
[TABLE="width: 258"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Staff[/TD]
[TD]Leave taken[/TD]
[/TR]
[TR]
[TD]03-Apr-18[/TD]
[TD]AA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]06-Apr-18[/TD]
[TD]BB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18-Apr-18[/TD]
[TD]EE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]06-Apr-18[/TD]
[TD]BB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24-Apr-18[/TD]
[TD]BB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02-May-18[/TD]
[TD]CC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27-Apr-18[/TD]
[TD]CC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]28-Apr-18[/TD]
[TD]EE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30-Apr-18[/TD]
[TD]EE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]01-May-18[/TD]
[TD]CC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]09-May-18[/TD]
[TD]CC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]09-May-18[/TD]
[TD]DD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11-May-18[/TD]
[TD]EE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14-May-18[/TD]
[TD]DD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15-May-18[/TD]
[TD]DD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16-May-18[/TD]
[TD]DD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17-May-18[/TD]
[TD]DD[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Output expected
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]AA[/TD]
[TD="class: xl65, width: 64"]BB[/TD]
[TD="class: xl65, width: 64"]CC[/TD]
[TD="class: xl65, width: 64"]DD[/TD]
[TD="class: xl65, width: 64"]EE[/TD]
[/TR]
[TR]
[TD="class: xl65"]April[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]May[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
</tbody>[/TABLE]

Any help will be appreciated.

regards

Vinod

 

[steve the fish]

Place the first table into A1:C18. The second table into A21:F23. In B22 place:

=COUNTIFS($A$2:$A$18,">="&"1"&$A22&YEAR(TODAY()),$A$2:$A$18,"<"&EDATE("1"&$A22&YEAR(TODAY()),1),$B$2:$B$18,B$21)

 

[steve the fish]

And if you need a sum of leave taken its:

=SUMIFS($C$2:$C$18,$A$2:$A$18,">="&"1"&$A22&YEAR(TODAY()),$A$2:$A$18,"<"&EDATE("1"&$A22&YEAR(TODAY()),1),$B$2:$B$18,B$21)