#Value error because headline is text

[tonilony]

Hello Forum-Members,

I was wondering if you could help me out with an error. I would like to count number of years in a date colum with a headline:

Date Headline
03.06.2010
01.10.2015
07.07.2015
03.06.2011

2010 = 1, 2015 = 2, 2011 = 1

I calculate this with the formular SUMMENPRODUKT(--(JAHR(B:B)=D2)) which returns an error message #value as the headline is text. Unfortunately this is part of a bigger table and I need to have the headline in place. Also there will be some new rows that why I use B:B.

Any ideas?

Thx, Anton

[berlan]

Hi, and welcome to the forum. Could you upload an example spreadsheet please?

[Tony Valko]

Try this array formula**:

=SUM(IF(ISNUMBER(B:B),IF(YEAR(B:B)=D2,1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[tonilony]

Awesome! Thx, it works great