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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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,378
Messages
5,486,518
Members
407,550
Latest member
LucasBordure

This Week's Hot Topics

Top