Count cells with a specific year

1. Count cells with a specific year

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 ?

2. Re: Count cells with a specific year

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))

3. Re: Count cells with a specific year

NBVC: great! Thank you, once again!

4. Re: Count cells with a specific year

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

5. Re: Count cells with a specific year

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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1