Count formula required that falls within a date range

[rajtak]

I need a formula to count cells of other columns that falls within the dates of July month. Please check the screenshot below and let me know your suggestions. It should ONLY count the non-blank cells of Micky



Thanks in advance

 

[Aladin Akyurek]

1. just enter:

=SUMIFS($C$2:$C$6,$A$2:$A$6,">="&"1-Jul-2013",$A$2:$A6,"<="&EOMONTH("1-Jul-2013",0))

2. Control+shift+enter, not just enter:

=SUM(IF($A$2:$A$6-DAY($A$2:$A$6)+1="1-Jul-2013"+0,$C$2:$C$6))

If so desired or needed, C2:C6 can be calculated by using Micky as look up value.

 

[Trevor G]

Also try this

Sheet1

*	A	B	C	D	E
*	*	Count Empty
*	*
*	*
*	*	*
*	*
*	*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="align: right"]01/07/2013
[/TD]
[TD="align: right"]30/07/2013
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: right"]01/07/2013
[/TD]

[TD="align: right"]2
[/TD]

[TD="align: right"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: right"]15/07/2013
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: right"]27/07/2013
[/TD]

[TD="align: right"]6
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: right"]01/08/2013
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]7
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: right"]05/08/2013
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]2
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =SUMPRODUCT((B2:B6<>"")*(A2:A6>=B1)*(A2:A6<=C1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[rajtak]

Thank you for your suggestions.
@Aladin Akyurek - I am looking for a formula that can count the number of non-blank cells NOT sum of the numbers in the cells. In other words, Micky has 3 values within date range of July month so the result should be '3'
@Trevor G - Your formula is not working for me.

 

[rajtak]

Thank you for your suggestions.
@Aladin Akyurek - I am looking for a formula that can count the number of non-blank cells NOT sum of the numbers in the cells. In other words, Micky has 3 values within date range of July month so the result should be '3'
@Trevor G - Your formula is not working for me.

 

[Trevor G]

Change the formula to

=SUMPRODUCT(ISBLANK(B2:B6)*(A2:A6>=B1)*(A2:A6<=C1))

 

[Aladin Akyurek]

1. just enter:

=SUMIFS($C$2:$C$6,$A$2:$A$6,">="&"1-Jul-2013",$A$2:$A6,"<="&EOMONTH("1-Jul-2013",0))

2. Control+shift+enter, not just enter:

=SUM(IF($A$2:$A$6-DAY($A$2:$A$6)+1="1-Jul-2013"+0,$C$2:$C$6))

If so desired or needed, C2:C6 can be calculated by using Micky as look up value.

Thank you for your suggestions.
@Aladin Akyurek - I am looking for a formula that can count the number of non-blank cells NOT sum of the numbers in the cells. In other words, Micky has 3 values within date range of July month so the result should be '3'
@Trevor G - Your formula is not working for me.

1a) Just enter:

=COUNTIFS($C$2:$C$6,"<>",$A$2:$A$6,">="&"1-Jul-2013",$A$2:$A6,"<="&EOMONTH("1-Jul-2013",0))

2a) Control+shift+enter:

=SUM(IF($A$2:$A$6-DAY($A$2:$A$6)+1="1-Jul-2013"+0,IF(ISNUMBER($C$2:$C$6),1)))

 

[rajtak]

I've figured it out myself for July

=COUNTIFS(A:A,"<=7/31/2013",C:C,"<=7/31/2013")

 

[Aladin Akyurek]

I've figured it out myself for July

=COUNTIFS(A:A,"<=7/31/2013",C:C,"<=7/31/2013")

Not well-formed; See my last reply.

 

[rajtak]

This also works for August

=COUNTIFS(A:A,">7/31/2013",A:A,"<=8/31/2013",C:C,"<=8/31/2013")

Thanks for your help Aladin Akyurek