count number of occurences of a month in a column only if in current year

[branbran]

Hi there: I have a column which is labeled "Date Assigned" in which I enter a date eg. 1/10/2014. I want to total the number of occurrences of a month in this column based on what I have chosen in my pick list that lists all the months, however only if we are in the current year. This column will have dates from 2013 and will continue to have dates in 2015.
I've made a pick list of all the months (January -December) and I want to be able to pick month and have it calculate the number of occurrences of that month in the "date assigned" column if we are in the current year. The current year stipulation is so I don't have to hard code the year each January. I'm using excel 2007 and am having a really hard time getting this to work. Any ideas??

 

[bbott]

Welcome to the board.

Assuming dates in column A and the month name (e.g. October) in B1, try:

=SUMPRODUCT(--(MONTH(1&B1)=MONTH(A1:A1000)),--(YEAR(A1:A1000)=YEAR(TODAY())))

 

[branbran]

humm, that's not working for me. I'm getting a #value! and I replicated your example with the dates in column A and October in B1 on a new sheet. This is what I tried to write but it is not working: =IF(B9="October",IF(YEAR(TODAY()),(COUNTIFS(Active!F:F,">=1/10/2014",Active!F:F,"<=31/10/2014")),0),0). I'm wondering if the date functions I'm using don't work in 2007. is there any way to tweak what I have written?

 

[bbott]

humm, that's not working for me. I'm getting a #value! and I replicated your example with the dates in column A and October in B1 on a new sheet.

That's odd. The only way I can get my formula to throw a #VALUE error is if the month is spelled incorrectly. What happens when you input the following?

A1: 10/1/2014
A2: 10/2/2014
A3: 10/3/2014
B1: October
C1: =SUMPRODUCT(--(MONTH(1&B1)=MONTH(A1:A1000)),--(YEAR(A1:A1000)=YEAR(TODAY())))

**make sure to copy the formula. Re-typing sometimes leads to accidental omission of characters.

There should be no issues using this formula in 2007.

 

[barry houdini]

You'd get #VALUE! error with any text in the range A1:A1000, e.g. a text header in A1

Try this version with COUNTIFS which will ignore any text

=COUNTIFS(A:A,">="&(1&B1)+0,A:A,"<="&EOMONTH((1&B1)+0,0))

 

[bbott]

You'd get #VALUE! error with any text in the range A1:A1000, e.g. a text header in A1.

Ah, indeed you will! Thanks Barry.

PS I like your version that uses the serial numbers of the dates.

 

[branbran]

Wow, this is great, thank you! much different than mine. It works although I'm a little lost with the formua, could you explain it out for me??

 

[branbran]

Wow, this is great, thank you! much different than mine. It works although I'm a little lost with the formua, could you explain it out for me??

just to be specific I know what countifs does, but I'm not sure how you confirm we are in the current year?I'm also not familliar with the use of the & symbol?

 

[barry houdini]

& is used to concatenate so let's assume you have October in B1 then 1&B1 just gives you the text string "1October"....but when you perform a calculation on a text string that looks like a date, i.e (1&B1)+0 you get a real date, 1st October in the current year

....so ">="&(1&B1)+0 resolves to the same as ">=1-Oct-2014" and from "<="&EOMONTH((1&B1)+0,0) you get "<=31-Oct-2014", so a count of dates within the month in question in the current year

 

[branbran]

that's great, this totally clears it up! Thanks!!