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

ccreations2

New Member
Joined
Jul 14, 2013
Messages
2
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


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

ABCD
1DateValueMonth to sumsum
21/1/201911/1/20194
31/15/201932/1/20192
42/1/201913/1/20195
52/28/20191
63/1/20192
73/31/20193
8

<tbody>
</tbody>

Thank You
 

Some videos you may like

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
Joined
Jun 17, 2019
Messages
308
Hi, you are very close, this should work:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Value</td><td style=";">Month to count</td><td style=";">count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1/1/2019</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">1/1/2019</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1/15/2019</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">2/1/2019</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2/1/2019</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">3/1/2019</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2/28/2019</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">3/1/2019</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">3/31/2019</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$A$2:$A$7,">="&C2,$A$2:$A$7,"<="&EOMONTH(<font color="Red">C2,0</font>),$B$2:$B$7,TRUE</font>)</td></tr></tbody></table></td></tr></table><br />
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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
Joined
Jul 14, 2013
Messages
2
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
Joined
Jun 17, 2019
Messages
308
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Value</td><td style=";">Month to count</td><td style=";">count</td><td style=";">Name</td><td style=";">ABC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1/1/2019</td><td style=";">ABC</td><td style="text-align: right;;">1/1/2019</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1/15/2019</td><td style=";">ABC</td><td style="text-align: right;;">2/1/2019</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2/1/2019</td><td style=";">DEF</td><td style="text-align: right;;">3/1/2019</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2/28/2019</td><td style=";">ABC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">3/1/2019</td><td style=";">ABC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">3/31/2019</td><td style=";">DEF</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$2:$A$7 >= C2</font>)*(<font color="Red">$A$2:$A$7 <= EOMONTH(<font color="Green">C2,0</font>)</font>)*(<font color="Red">$B$2:$B$7=$F$1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

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

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...
Top