# countif month

#### charliebuoy

##### New Member
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)

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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))

Last edited:
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????

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

Have you tried the EDATE function?

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

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

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

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.

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

Code:
``````[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
Code:
``````[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]``````

Last edited:
hi

not sure what i am doing wrong now - from the following dates all the formulas return 0
 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 May-15 May-15 Jun-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16

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



<TBODY>
</TBODY>

Replies
6
Views
200
Replies
7
Views
230
Replies
6
Views
151
Replies
6
Views
223
Replies
3
Views
117

Threads
1,203,029
Messages
6,053,125
Members
444,640
Latest member
Dramonzo

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

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