# Thread: how to Count cells in column B based on date in column A Thanks:  2 Post #5312239 (1)Post #5312242 (1) Likes:  2 Post #5312242 (1)Post #5312239 (1)

1. ## how to Count cells in column B based on date in column A

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

 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 8

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))

 A B C D 1 Date Value Month to sum sum 2 1/1/2019 1 1/1/2019 4 3 1/15/2019 3 2/1/2019 2 4 2/1/2019 1 3/1/2019 5 5 2/28/2019 1 6 3/1/2019 2 7 3/31/2019 3 8

Thank You

2. ## Re: how to Count cells in column B based on date in column A

Hi, you are very close, this should work:

ABCD
1DateValueMonth to countcount
21/1/2019TRUE1/1/20192
31/15/2019TRUE2/1/20191
42/1/2019TRUE3/1/20191
52/28/2019FALSE
63/1/2019TRUE
73/31/2019FALSE

Sheet6

Worksheet Formulas
CellFormula
D2=COUNTIFS(\$A\$2:\$A\$7,">="&C2,\$A\$2:\$A\$7,"<="&EOMONTH(C2,0),\$B\$2:\$B\$7,TRUE)

3. ## Re: how to Count cells in column B based on date in column A

Just another option:

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

4. ## Re: how to Count cells in column B based on date in column A

Originally Posted by Aryatect
Just another option:

Code:
`=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?

5. ## Re: how to Count cells in column B based on date in column A

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:

ABCDEF
1DateValueMonth to countcountNameABC
21/1/2019ABC1/1/20192
31/15/2019ABC2/1/20191
42/1/2019DEF3/1/20191
52/28/2019ABC
63/1/2019ABC
73/31/2019DEF

Sheet2

Worksheet Formulas
CellFormula
D2=SUMPRODUCT((\$A\$2:\$A\$7 >= C2)*(\$A\$2:\$A\$7 <= EOMONTH(C2,0))*(\$B\$2:\$B\$7=\$F\$1))