COUNTIF with extract year from date

[Tripp]

Hello,

I am trying to create a COUNTIF formula that will count the number of times the year 2015 is present in a list. The Column contains dates in the format "dd/mm/yyyy hh:mm" and I was hoping to extract the date and count in the same formula.

I was trying with COUNTIF(A1:A10, year(A1)="2015") but this yields no results. I'm sure I can do it with a helper column but am interested to see if it can be done without.

Regards,

Tripp

 

[Luke M]

Can't do that with just COUNTIF. You can either create a helper column with formula
=YEAR(A1)

and then base the COUNTIF off of that, or you can use SUMPRODUCT like
=SUMPRODUCT(1*(YEAR(A1:A10)=2015))

 

[Tripp]

I always forget sumproduct!

Cheers Luke

 

[Khalid NGO]

Hi Tripp,

Or you can use the COUNTIFS like this:

=COUNTIFS(A1:A10,">=1/1/2015",A1:A10,"<=31/12/2015")

Regards,

 

[sureshsonti]

Can't do that with just COUNTIF. You can either create a helper column with formula
=YEAR(A1)

and then base the COUNTIF off of that, or you can use SUMPRODUCT like
=SUMPRODUCT(1*(YEAR(A1:A10)=2015))

Hey Luke M
What does 1 mean in this formula

=SUMPRODUCT(1*(YEAR(A1:A10)=2015))

Thanks

 

[shrivallabha]

Hey Luke M
What does 1 mean in this formula

=SUMPRODUCT(1*(YEAR(A1:A10)=2015))

Thanks

(YEAR(A1:A10)=2015) returns an array of Boolean values like {TRUE,FALSE,TRUE,...,TRUE}.

To calculate SUM the results need to be converted to numeric ones. This multiplication by 1* forces the results to convert to numeric array like {1,0,1,...,1}.

You can also use following options
Add zero
=SUMPRODUCT((YEAR(A1:A10)=2015)+0)
Use double unary
=SUMPRODUCT(--(YEAR(A1:A10)=2015))

Hope it helps!

 

[Peter Bartholomew]

You can also use following options

Whilst we are collecting options for converting the Boolean TRUE to the number 1 there are a couple of functions that also work
= SUMPRODUCT(N(YEAR(dates)=2015))
= SUMPRODUCT(SIGN(YEAR(dates)=2015))

As a variation on COUNTIF, the two separate functions
{= COUNT(IF(YEAR(dates)=2015,1))}
work provided they are entered as an array formula