countif with date ranges of indeterminate length

[kualjo]

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?

 

[Weaver]

try

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

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

 

[kualjo]

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]

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]

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]

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]

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

 

[jeffreybrown]

Wrong formula

 

[BGY23]

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]

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.