# Need a formula to count the dates that occurred last month

#### freeb1893

##### Board Regular
I have a column of dates. I want to create a formula where it looks at that column of dates, and counts the number of cells where the date occurred in the month prior to today. Anyone know how to put that into a formula?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### NeonRedSharpie

##### Well-known Member
Code:
``=sumproduct(--(isnumber(a1:a51)),--(month(a1:a51)=(month(today())-1)))``

#### freeb1893

##### Board Regular
Perfect! Also do you know how I could do the same thing, but also reference the column/cell adjacent A1:A51, and only count if the date in the adjacent column B also occurred in the month prior to today?

Code:
``=sumproduct(--(isnumber(a1:a51)),--(month(a1:a51)=(month(today())-1)))``

#### NeonRedSharpie

##### Well-known Member
Code:
``=SUMPRODUCT(--(ISNUMBER(I17:I51)),--(ISNUMBER(H17:H51)),--(MONTH(H17:H51)=(MONTH(TODAY())-1)),--(MONTH(I17:I51)=(MONTH(TODAY())-1)))``

You probably don't need the isnumber, it just checks to make sure a cell is not blank.

#### Scott Huish

##### MrExcel MVP
=SUMPRODUCT(--(MONTH(A1:A51)=MONTH(TODAY())-1),--(MONTH(A1:A51)=MONTH(B1:B51)))

Please note that none of these formulas will check to see if the two dates are in the same year or not.

#### freeb1893

##### Board Regular
Hmm something is hanging up this formula. I'm getting the #VALUE! error. Below is some example data. The result of the formula should be 2.

 Final Submission Initial Start 6/5/2014 6/3/2014 6/19/2014 6/13/2014 6/26/2014 6/2/2014 6/26/2014 6/2/2014 7/17/2014 6/17/2014 7/14/2014 7/14/2014 7/2/2014 7/2/2014 7/24/2014 7/2/2014 7/30/2014 7/2/2014 8/5/2014 7/29/2014 8/22/2014 8/15/2014 8/22/2014 8/13/2014 9/2/2014 7/9/2014 6/26/2014 7/23/2014 7/9/2014 7/9/2014 7/29/2014

<tbody>
</tbody><colgroup><col><col></colgroup>
=SUMPRODUCT(--(MONTH(A1:A51)=MONTH(TODAY())-1),--(MONTH(A1:A51)=MONTH(B1:B51)))

Please note that none of these formulas will check to see if the two dates are in the same year or not.

Replies
3
Views
672
Replies
2
Views
520
Replies
19
Views
548
Replies
2
Views
245
Replies
0
Views
195

1,191,578
Messages
5,987,403
Members
440,096
Latest member
yanaungmyint

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

### Which adblocker are you using?

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

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