countif month

[charliebuoy]

Hi,

Could anyone help me with this please?

I am trying to count the nummber of cells in a column where the date is the current month, one month ahead, and if the month has past (the results can be displayed in 3 different cells)

ie;
A1 = Jun-15
B1 = May-15
C1 = Jul-15
D1 = Jun-15

so A2 = 2 (current month) A3 = 1 (1 month ahead) A4 = (past months)

 

[gaz_chops]

Welcome to the forum, try these entered as array formulas (Ctrl, Shift & Enter)

Current Month
=COUNT(IF(MONTH(A1:D1)=MONTH(TODAY()),A1:D1))

+1
=COUNT(IF(MONTH(A1:D1)+1=MONTH(TODAY()),A1:D1))

Past
=COUNT(IF(MONTH(A1:D1) < MONTH(TODAY()),A1:D1))

 

[charliebuoy]

hi,

thank you for the prompt reply, sorry it does not quite do as i needed.

I need to show the number of occurrences where the past month & future months are present, regardless of how many days apart they are, so if the current date is 15-Jun-15, if the cell i am looking at is July-15 as if defaults to 0/Jul/15 the formula above thinks it is the same month????

 

[gaz_chops]

hi,

thank you for the prompt reply, sorry it does not quite do as i needed.

I need to show the number of occurrences where the past month & future months are present, regardless of how many days apart they are, so if the current date is 15-Jun-15, if the cell i am looking at is July-15 as if defaults to 0/Jul/15 the formula above thinks it is the same month????

Explain your logic? July=month7 June=Month6?????????????????????

 

[taylorpa5]

Have you tried the EDATE function?

<colgroup><col width="110"></colgroup><tbody>
</tbody>

 

[gaz_chops]

Sorry I will leave this for someone else, I answered your original question but your reply is ????

 

[gaz_chops]

The one i got wrong is +1 (although your response did not relay this)!

=COUNT(IF(MONTH($A$1:$A$4)+1=MONTH(TODAY()+1),$A$1:$A$4))

 

[charliebuoy]

hi Gaz, sorry for the confusion, i have a report that has hundreds of actions with due dates, i needed to see a way of counting the number of due dates that were next month, the number that had gone past this month and the number that were due this month.

 

[gaz_chops]

Think I was too tired last night! Which formula don't work?
The last one should be as below

[TABLE="width: 455"]
<tbody>[TR]
[TD]=Month[/TD]
[TD="colspan: 5"]=COUNT(IF(MONTH($A$1:$A$4)=MONTH(TODAY()),$A$1:$A$4))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=Month+1[/TD]
[TD="colspan: 6"]=COUNT(IF(MONTH($A$1:$A$4)=MONTH(TODAY())+1,$A$1:$A$4))[/TD]
[/TR]
[TR]
[TD]<  Month[/TD]
[TD="colspan: 5"]=COUNT(IF(MONTH($A$1:$A$4) < MONTH(TODAY()),$A$1:$A$4))[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

They return

[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 65, align: right"]01/04/15[/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"]=Month[/TD]
  [TD="width: 65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]05/06/15[/TD]
  [TD][/TD]
  [TD]=Month+1[/TD]
  [TD="align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]05/07/15[/TD]
  [TD][/TD]
  [TD] < Month[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]08/07/15[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[charliebuoy]

hi

not sure what i am doing wrong now - from the following dates all the formulas return 0

Feb-15</SPAN>

Mar-15</SPAN>

Apr-15</SPAN>

May-15</SPAN>

Jun-15</SPAN>

Jul-15</SPAN>

May-15</SPAN>

May-15</SPAN>

Jun-15</SPAN>

Jun-15</SPAN>

Jul-15</SPAN>

Aug-15</SPAN>

Sep-15</SPAN>

Oct-15</SPAN>

Nov-15</SPAN>

Dec-15</SPAN>

Jan-16</SPAN>

Feb-16</SPAN>

Mar-16</SPAN>

Apr-16</SPAN>

May-16</SPAN>

Jun-16</SPAN>

Jul-16</SPAN>

Aug-16</SPAN>

Sep-16</SPAN>

Oct-16</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=25></COLGROUP>

</SPAN>

<TBODY>
</TBODY>