count if date in another cell falls in certain month and year

**ea223** · 11-13-2012, 12:49 AM

I have a large spreadsheet where one column is just dates spanning over a year.
Column A = dates (e.g. 18/5/12)
Column B = status
I want a formula that counts everything with a status of 'overdue' (in column B) that is in January 2012.
I've tried sumproduct etc but it's not working as of yet.
Thank you

**FDibbins** · 11-13-2012, 01:02 AM

try something like this, adjust ranges as needed...
=COUNTIFS(B1:B35,"overdue",A1:A35,">=1/1/12",A1:A35,"<=1/31/12")

there are 2 variations on this that I can offer.
- put the 2 dates in their own cells and then reference them
- use a helper column to extract the month number, then use =COUNTIFS(B1:B1,"overdue",A1:A35,1) and again the 1 (month) can be put in it's own cell and referenced

hope this helps?

**benishiryo** · 11-13-2012, 01:09 AM

hi ea223. you can use the SUMPRODUCT as such:
=SUMPRODUCT((TEXT(A1:A10,"mmmyy")="Jan12")*(B1:B10="Overdue"))

**ea223** · 11-13-2012, 01:46 AM

thank you! this works just fine.
sorry fdibbins I think I was unclear but your help is useful to me in other ways!

**FDibbins** · 11-13-2012, 01:58 AM

glad that you got the answer you wanted. just for the record, what did I not understand about what you wanted?

**ea223** · 11-13-2012, 02:04 AM

Hi fdibbins, sorry I think you actually did understand, I just misread what you wrote. It actually works just fine too, thank you. Do you by any chance know anything about how to get graphs to update automatically, I posted under the charts forum but have had no response as of yet.
Thanks

**FDibbins** · 11-13-2012, 11:32 AM

Hi. can you post the thread link here?

**ea223** · 11-13-2012, 11:51 PM

yes, this is what I posted:
Hi all.
I have a spreadsheet that when you select a month in cell A1 (from a dropdown list) the data in the rest of the sheet will automatically update. This works fine but I also want to incorporate charts into my sheet. So what I would like is for the chart to automatically update and show information for any given month when that month is selected in cell A1. Is this possible?
Thank you

also the formulas in this thread work but they are making my excel crash, any ideas what's going on here?!
thanks