Count ifs formula

[Sparda142]

Hello,

What formula can i use to count the number of individuals joining by month and year in column E. I've tried the formula below however get a value of Zero

=COUNTIFS($Q$16:$Q$4007,MONTH(6),$S$16:$S$4007,YEAR(2019))

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6[/TD]
[TD]2019[/TD]
[TD]john[/TD]
[TD]June[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]2019[/TD]
[TD]jacob[/TD]
[TD]May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]2019[/TD]
[TD]jill[/TD]
[TD]March[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]2019[/TD]
[TD]bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

If the values in your columns look like you have shown (i.e. 6 and 2019), then those are just numbers and not dates.
So you would not use the MONTH and YEAR function in the criteria of your COUNTIFS formula, simple use those numbers.

 

[DanteAmor]

According to your example, you have the following situation.
In column A you have the month number and in column D you have the name of the month.
Then we must convert the name of the month to number.
Check the following formula


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:104.55px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:84.59px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff99; font-weight:bold; text-align:center; ">Month Number</td><td style="background-color:#ffff99; font-weight:bold; text-align:center; ">Year</td><td style="background-color:#ffff99; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffff99; font-weight:bold; text-align:center; ">Mont Name</td><td style="background-color:#ffff99; font-weight:bold; text-align:center; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">6</td><td style="text-align:right; ">2019</td><td >john</td><td >June</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">6</td><td style="text-align:right; ">2019</td><td >jacob</td><td >May</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">2019</td><td >jill</td><td >March</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3</td><td style="text-align:right; ">2019</td><td >bob</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td><td style="text-align:right; ">2019</td><td >Pau</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td style="text-align:right; ">2019</td><td >Mat</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">5</td><td style="text-align:right; ">2019</td><td >Nei</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">5</td><td style="text-align:right; ">2019</td><td >Sus</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=COUNTIFS($A$2:$A$9,MONTH(D2&1),$B$2:$B$9,2019)</td></tr></table></td></tr></table>