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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi, you are very close, this should work:


Book1
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
Cell Formulas
RangeFormula
D2=COUNTIFS($A$2:$A$7,">="&C2,$A$2:$A$7,"<="&EOMONTH(C2,0),$B$2:$B$7,TRUE)
 
Upvote 0
Just another option:

Code:
=SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=TRUE))
 
Upvote 0
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?
 
Upvote 0
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
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
Cell Formulas
RangeFormula
D2=SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=$F$1))
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top