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

Date	Staff	Leave taken
03-Apr-18	AA	1
06-Apr-18	BB	1
18-Apr-18	EE	1
06-Apr-18	BB	1
24-Apr-18	BB	1
02-May-18	CC	1
27-Apr-18	CC	1
28-Apr-18	EE	1
30-Apr-18	EE	1
01-May-18	CC	1
09-May-18	CC	1
09-May-18	DD	1
11-May-18	EE	1
14-May-18	DD	1
15-May-18	DD	1
16-May-18	DD	1
17-May-18	DD	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Output expected

Month	AA	BB	CC	DD	EE
April	1	3	1		3
May			3	5	1

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

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)