Count cells with a specific year

**Saturn** · 04-15-2011, 08:47 AM

I have a table with cells with date-format (d-mmm-yyyy).
I want to find the number of celles which refers to a specific year using formula COUNTIF.
Can somebody help me with the formula ?

**NBVC** · 04-15-2011, 08:59 AM

If you have to use COUNTIF, then perhaps something like:

=COUNTIF(A:A,">="&DATE(2011,1,1))-COUNTIF(A:A,">"&DATE(2011,12,31))

or you can use Sumproduct:

=Sumproduct(--(Year(A1:A100)=2011))

**Saturn** · 04-15-2011, 09:32 AM

NBVC: great! Thank you, once again!

**Saturn** · 04-17-2011, 07:25 AM

I have reopened this thread.

I have two questions:
1: what does the "--" in ..(--(YEAR.. stand for?
2: I want to add sum of kilometer for each year. Please see the attachment. I have tried SUMPRODUCT and SUMIFS without success.

Saturn

**NBVC** · 04-17-2011, 09:33 PM

The -- is a double unary. It is used to coerce TRUE and FALSE results from the conditional checks to 1's and 0's, respectively, so that the match can be performed.

Try:

=SUMPRODUCT(--(YEAR($B$4:$B$16)=F4),$C$4:$C$16)