how to Count cells in column B based on date in column A

[ccreations2]

Hello,
in cells A2:A200 i have dates, than in cells B2:B200 it either "TRUE or FALSE"
im looking for a formula in cell D4 to count how many TRUE cells per month in cell C3
see below how i need it to calculate


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]Month to count[/TD]
[TD]count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2019[/TD]
[TD]TRUE[/TD]
[TD]1/1/2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/15/2019[/TD]
[TD]TRUE[/TD]
[TD]2/1/2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2/1/2019[/TD]
[TD]TRUE[/TD]
[TD]3/1/2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2/28/2019[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3/1/2019[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3/31/2019[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i figured out the same idea is sum function as follows but need your help count function

in cell D2 =SUMIFS($B$2:$B$7,$A$2:$A$7,">="&C2,$A$2:$A$7,"<="&EOMONTH(C2,0))
in cell D3 =SUMIFS($B$2:$B$7,$A$2:$A$7,">="&C3,$A$2:$A$7,"<="&EOMONTH(C3,0))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]Month to sum[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[TD]1/1/2019[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/15/2019[/TD]
[TD]3[/TD]
[TD]2/1/2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2/1/2019[/TD]
[TD]1[/TD]
[TD]3/1/2019[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2/28/2019[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3/1/2019[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3/31/2019[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank You

 

[Aryatect]

Hi, you are very close, this should work:

Book1
	A	B	C	D
1	Date	Value	Month to count	count
2	1/1/2019	TRUE	1/1/2019	2
3	1/15/2019	TRUE	2/1/2019	1
4	2/1/2019	TRUE	3/1/2019	1
5	2/28/2019	FALSE
6	3/1/2019	TRUE
7	3/31/2019	FALSE
Sheet6

Cell Formulas
Range	Formula
D2	=COUNTIFS($A$2:$A$7,">="&C2,$A$2:$A$7,"<="&EOMONTH(C2,0),$B$2:$B$7,TRUE)

 

[Aryatect]

Just another option:

=SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=TRUE))

 

[ccreations2]

Just another option:

=SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=TRUE))

Thank You, amazing! worked well....
I like the =sumproduct formula better, because im able to count column c as well
just 1 more question Mr Genius can this formula work with count cell with any value? not a specific like true, false, or at specified in the formula?

 

[Aryatect]

Hi,

Glad could help! It can work with any value, instead of specifying TRUE or FALSE in a formula you can refer it to any cell, extending your problem where say column B had names and we wanted to find result for specific name then the same formula can be reused as shown below:

Book1
	A	B	C	D	E	F
1	Date	Value	Month to count	count	Name	ABC
2	1/1/2019	ABC	1/1/2019	2
3	1/15/2019	ABC	2/1/2019	1
4	2/1/2019	DEF	3/1/2019	1
5	2/28/2019	ABC
6	3/1/2019	ABC
7	3/31/2019	DEF
Sheet2

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=$F$1))