Count or Sum Number of Entries per Year

Hi,

I have a large log between 2012 & 2013 & soon into 2014.

I attempted to use the following functions, but neither is giving desires results. Do these functions not read dates?


=COUNT($C$16:$C$10000,"*2013*")

=COUNTIFS($C$16:$C$10000,"*2013*")


=SUMIF($C$16:$C$10000,"*2012*",$C$16:$C$10000)

Please advise & even better provide a function.

Thanks

Hi,

a possible function could be

=SUMPRODUCT(--(YEAR($C$16:$C$10000)=2013))

Hope it helps

Why not use a Pivot Table and group by years?

Hi CANAPONE,

This is the type of simplistic function that I was seeking & works well!!!

Thanks

Pepe Le Mokko

-- As for Pivot Tables - They would also work but the way that I have my log file & calculations would radically change my formats.

Thanks

Hi,

thanks for your kind feedback.

Ciao

CANAPONE's solution is ideal, however if you want to stick to the COUNT function.. try this array formula

=COUNT(IF(YEAR($C$16:$C$10000)=2013,$C$16:$C$10000))
or simply
=COUNT(IF(YEAR($C$16:$C$10000)=2013,))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

or this regular formula
=COUNTIFS($C$16:$C$10000,">="&DATE(2013,1,1),$C$16:$C$10000,"<="&DATE(2013,12,31))