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

#### ccreations2

##### New Member
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

<tbody>
</tbody>

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

<tbody>
</tbody>

Thank You

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Aryatect

##### Active Member
Hi, you are very close, this should work:

#### Aryatect

##### Active Member
Just another option:

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

#### ccreations2

##### New Member
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?

#### Aryatect

##### Active Member
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:

1,102,642
Messages
5,488,071
Members
407,622
Latest member

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...