Count columns by condition

Question

I have a column (A) that contains a series of dates in the format mm/dd/yyyy. I want to run a count to return how many of them were in 2013.

Is this possible?

I tried using this formula, but it didn't work:

=COUNTIF (ARRAYFORMULA ( LEFT (C:C, 4)), "2013")

Answer 1

In addition to the solution provided by OnenOnlyWalter, here's another formula:

=COUNTIF(ARRAYFORMULA(YEAR(A:A)),"2013")

The YEAR function simply returns the year in 4 digits. The ARRAYFORMULA will just take on the complete column and the COUNTIF will finish it off by counting the occurrences.

If you're using a header, then you can adjust the above formula like this:

=COUNTIF(ARRAYFORMULA(YEAR(A2:A)),"2013")

Answer 2

It looks like you have the right idea but the formula needs a little tweaking.

Try this:

=ARRAYFORMULA(COUNTIF((RIGHT(TEXT(C:C,""),4)),"2013"))

Some things to point out: - For a MM/DD/YYYY format, you need to take the right character, not left - The cell contents for dates are not strings, so you need to use TEXT() to convert the dates

This worked on my test sheet, let me know if it works for you or if I need to tweak it a bit.