# countif with date ranges of indeterminate length

#### kualjo

##### Board Regular
I am having trouble writing a COUNTIF formula that will tell me how many times a particular month appears in a list of dates. January may appear 10 times, February may appear 43 times, March 102 times, etc. Instead of manually selecting the range for each month and using the count feature on the status bar, I need a formula that will count for me. I tried:
COUNTIF(B:B,month(1))
COUNTIF(B:B,month=1)
as well as several other lame attempts, but kept getting a result of zero or an error. What am I missing here?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Weaver

##### Well-known Member
try

=SUMPRODUCT(--(MONTH(\$B\$1:\$B\$1000)=1))

you can't use full columns with this formula unless you're on 2007

#### kualjo

##### Board Regular
Can SUMPRODUCT be used to count? I tried your suggestions and got a #VALUE! error, so I'm still stuck. This probably doesn't matter, but in case it does, I should mention that the dates are in the 01/01/2009 format.

#### Weaver

##### Well-known Member
You can use sumproduct to count things, as the --() construct converts the enclosed function to a 0 or 1 depending on the result, so you're effectively counting the times your condition is true.

Did you change anything when you tried the formula?

Unless you're using 2007, you can't use whole columns as the given range in the SUMPRODUCT function.

#### kualjo

##### Board Regular
I copied the formula straight out of your post. The way you describe SUMPRODUCT leads me to believe that it should have returned the count I need instead of an error. I originally had the full column in the range, but changed that to the 2:1000 range.
Why doesn't COUNTIF work in this situation? It seems that I should be able to use a formula like =COUNTIF(B2:B1000,MONTH(1)) or something similar.

#### Weaver

##### Well-known Member
try reformatting the date column to numbers and see if any remain looking like dates. This would suggest they're strings and might be what's causing the problem.

I know what you mean about COUNTIF, but I think it's constrained by having to have the condition contained in a string. Also, you have to apply the MONTH function to the range in question, which COUNTIF won't allow

#### Weaver

##### Well-known Member
also, I'd be tempted to test the year as well - as a blank cell is effectively 00/01/1900, so will return a positive if tested just for 'january'

=SUMPRODUCT(--(MONTH(\$B\$2:\$B\$1000)=1),--(YEAR(\$B\$2:\$B\$1000)=2009))

Which also shows why SUMPRODUCT is better than COUNTIF - multiple conditions

Wrong formula

Last edited:

#### BGY23

##### Well-known Member
A possible work around is to use a "helper" column.

In i.e. column A enter Month(b2) and copy it down. Now use your countif to count the number of 1's, 2's etc in column A.

You can insert a column, enter the formula top to bottom and then hide it so it doesn't show up on you prints etc.

#### kualjo

##### Board Regular
excelR8R: I found one entry that did not have the slash between month and year. My typo. So that's fixed, but your explanation of how blank cells would be counted as month=1 kills the usefulness of SUMPRODUCT for me. I don't put the date on every row, since there are usually multiple rows needed for each entry, and the date only needs to be on the first row. Also, by using a range of 2:1000 - which is intended to cover a range that will likely always be bigger than necessary - every blank cell from the bottom of my data down to row 1000 winds up in the count. I even tried subtracting blank cells with a COUNTIF, but that didn't work. Maybe I just have to count January manually and use SUMPRODUCT for the other months. Thanks for the help, though.

Replies
18
Views
379
Replies
4
Views
453
Replies
3
Views
511
Replies
3
Views
293
Replies
1
Views
254

1,191,366
Messages
5,986,244
Members
440,012
Latest member
StumpedGump1987

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