# COUNTIF and dates by month/year only (not days)

#### akparasite

##### Board Regular
Hello all,

This seems trivial, but I can't figure out the best way to get my needed result.

I need to count the number of instances a date occurs within a range, but the criteria to get the result must only be defined by month/year.

I was planning on using COUNTIF to get my result, but am unsure how to format the criteria portion of the formula to return all occurrences within that month/year.

Here is a sample workbook. I want to find all dates occurring within January-02, so the COUNTIF result should be 5 in this case:
Book1
ABCD
1SHIPDATE
21/14/2002January-02
31/16/2002
41/18/2002
51/20/2002
61/22/2002
72/14/2002
82/14/2002
92/14/2002
102/14/2002
112/14/2002
122/14/2002
Sheet1

Your help is appreciated as always!

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi,
Put 1/1/2002 in D1 for example
and 1/31/2002 in D2

=SUMPRODUCT((A2:A12>=D1)*(A2:A12<=D2))

Eli

That looks great. Do you think there's a way I could make it reference only one cell (i.e. Cell C1) without having to input beginning and end dates for each month?

I was playing with this formula since I submitted my initial post...but it doesn't work...

=SUMPRODUCT((TEXT(\$A\$2:\$A\$12,"YYYY")=TEXT(\$C\$2,"YYYY")*(TEXT(\$A\$2:\$A\$12,"MMM")=TEXT(\$C\$2,"MMM"))))

...obviously a problem with the formatting I'm calling upon. Any suggestions?
This message was edited by akparasite on 2002-08-22 23:19

On 2002-08-22 23:19, akparasite wrote:
That looks great. Do you think there's a way I could make it reference only one cell (i.e. Cell C1) without having to input beginning and end dates for each month?

I was playing with this formula since I submitted my initial post...but it doesn't work...

=SUMPRODUCT((TEXT(\$A\$2:\$A\$12,"YYYY")=TEXT(\$C\$2,"YYYY")*(TEXT(\$A\$2:\$A\$12,"MMM")=TEXT(\$C\$2,"MMM"))))

...obviously a problem with the formatting I'm calling upon. Any suggestions?
This message was edited by akparasite on 2002-08-22 23:19

=SUMPRODUCT((TEXT(A2:A12,"mmm/yyyy")=TEXT(C2,"mmm/yyyy"))*1)

Eli

Thank you Eli!!!!

Replies
7
Views
223
Replies
3
Views
101
Replies
2
Views
148
Replies
7
Views
65
Replies
3
Views
249

1,196,484
Messages
6,015,466
Members
441,898
Latest member
kofafa

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