Locating specific dates in a column

[datadummy]

I am trying to write a formula that will reference a column and give me a count of all cells that have the specified year in them. Example column H will various years worth of data in this format 1/01/2017 and I am attempting to get counts of each years total, so month and day are pretty much irrelevant.

 

[Joe4]

Try this formula, which is an array formula so needs to be entered with CTRL-SHIFT-ENTER:

=SUM(IF(YEAR(H:H)=2017,1))

If entered correctly, it will show curly brackets in the formula bar after entry, i.e.

{=SUM(IF(YEAR(H:H)=2017,1))}

 

[MrKowz]

Try:

Excel 2013/2016
	A	B	C	D
1	Date		Year	2017
2	1/1/2017		Count	3
3	2/25/2017
4	12/13/2016
5	11/28/2016
6	6/25/2017
Sheet17

Cell Formulas
Range	Formula
D2	=COUNTIFS(A:A,">="&DATE(D1,1,1),A:A,"<="&DATE(D1,12,31))

 

[datadummy]

I tried the equation and its returning #VALUE! . I entered it as you recommended and got the extra brackets to populate but something doesn't seem to be working. Could the fact that there are many blanks in the column through off the formula?

 

[datadummy]

I currently am running excel 2007, will this work in that version?

 

[Joe4]

Could the fact that there are many blanks in the column through off the formula?

When you say "blanks", do you mean empty cell, or spaces?
Any text entries might cause problems.
However, you can modify it and it should work.
Just change H:H to the actual range you want to check, i.e. H2:H100.
If we can get exclude any cells that have spaces or text in it, it should work.

 

[datadummy]

When you say "blanks", do you mean empty cell, or spaces?
Any text entries might cause problems.
However, you can modify it and it should work.
Just change H:H to the actual range you want to check, i.e. H2:H100.
If we can get exclude any cells that have spaces or text in it, it should work.

The blanks are empty cells.

 

[Joe4]

Did you try changing the range to just the cells you want to check.
Text entries (like Column Headings) will cause that error.

 

[datadummy]

Did you try changing the range to just the cells you want to check.
Text entries (like Column Headings) will cause that error.

I updated the formula with a cell range and it is still returning the error. Currently my formula looks like this {=SUM(IF(YEAR(H2:H5000)=2012,1))}.

 

[Joe4]

Are all the dates entered as dates (and not as Text that looks like dates)?
Are there any text entries anywhere between H2 and H5000?

If there are not, then if you enter these two formulas anywhere on your sheet:
{=SUM(IF(ISNUMBER(H2:H5000),1))}
=COUNTBLANK(H2:H5000)
they should add up to 4999.
If they do not, then you have some text entries somewhere in there.