Countif Date Range

Please can anyone help me with the following.

I'm basically trying to count all the dates in January in the 'Date Sent' column. See attached for details.

The formula I've used is: =COUNTIF(B:B, "*Jan") but the value it returns is zero & I don't know why?

Can anyone help.

Hi Lisa,

You can't use a text comparison in that case, because the dates are actually numbers. They are just shown as text. You can use for example this formula:

Please Login or Register  to view this content.

but there can be no empty cells (or cells that return an error when using MONTH-function) in the area.

- Asser

Hi Jazzer

Thanks very much for your reply. Unfortunately from time to time, the 'date sent' column will contain blanks because the order has not been sent yet.

Any ideas on how to get the formula to ignore blank cells in the 'date sent' column?

Your help would really be appreciated.

Thanks again.

Hi,

this will skip the empty cells

Please Login or Register  to view this content.

But, there can't be any text in the area. So if you use reference like B:B, you'll get an error, because there is this header in B1.

It is possible to skip those text cells also, but then this formula will get even more complicated. If you need that, just ask again and I or somebody else will help.

- Asser

HI Jazzer

That works a treat. Thank you very much!

Now I've got the formula to tell me all late quotes for January - I might need to perform the same calculation for other months. I.e How many late orders for Feb & so on..

How do I amend your formula to do this?

Thanks a lot for your help.

You're welcome.

Just change the number after the MONTH(...)=1 to what ever you need. 1=January, 2=February and so on.

- Asser

Any idea how can I countif it is January 2012 separately than january 2013? Meaning if I have 3 years of data I countif something shipped given month then I have 36 (12 months x 2010, 2011, 2012) month buckets and not just 12 ?