COUNTIFS with cells of yyyy-mm-dd

users
#1

hello,

I have yyyy-mm-dd as my date format in Calc (libreoffice 5.1.5.2) .

I like to count number of cells for a
certian year *only* ....

sample data :

2011-03-28
2013-08-19
2014-07-02
2015-01-11
2016-05-30

for any year, let say 2011, gives me err510
=COUNTIF(A1:A50,DATE(2011,*))

any help is appreciated.

F-

#2

Ho, ho - you are making it up!

o In B1, enter =TEXT(A1,"YYYY") and fill down the column.
o Use =COUNTIF(B1:B50,"2011") .

Or:
o In B1, enter =VALUE(TEXT(A1,"YYYY")) and fill down the column.
o Use =COUNTIF(B1:B50,2011) .

Note that the column here suggested as B can be hidden or put away elsewhere on the sheet or even on another sheet.

I trust this helps.

Brian Barker

#3

I was able to get this to work as I understand it?

                                                            03/28/11

                                                            08/19/13

                                                            07/02/14

                                                            01/11/15

                                                            05/30/16

                                                                   1
"=COUNTIFS(H1:H5,">="&DATE(2015,1,1),H1:H5,"<="&DATE(2015,12,31))"

I put the dates in column H in rows 1 to 5. (I have a actual date fields in
MM/DD/YY format.

Had to use the COUNTIFS instead of COUNTIF, since had to get the range.
Don't know if it would be possible to get the =(year) of the cells without
creating a second column with that?
Then set the two criterians to find matching values.

=COUNTIFS(H1:H5,">="&DATE(2015,1,1),H1:H5,"<="&DATE(2015,12,31))

Other option was to put in I1-i5 =year(h1) ti =year(h5), and then
=countif(i1:i5,2015) works.

Date sent: Wed, 5 Oct 2016 17:30:56 -0600

#4

Or, for example, for the year 2015 try this:
=COUNTIFS(A1:A50,">="&DATE(2015,1,1), A1:A50,"<="&DATE(2015,12,31))

#5

thank you all for your replies ...

using your suggestions following will
do what I wanted to accomplish:

=COUNTIFS(A10:A500,">="&DATE(2016,1,1),A10:A500,"<="&DATE(2016,12,31),F10:F500,"="&"ZZZ")

and I have another cell with the above using
'SUMIFS' instead , therfeore, working on data in three columns ...

thanks,
F-

#6

Or

=SUMPRODUCT(YEAR(A1:A50)=2011)

Miguel Ángel.

#7

And, for 2 criteria like this:
=SUMPRODUCT((YEAR(A1:A50)=2011)*(B1:B50="ZZZ"))

#8

Except that this - though it will work - is missing the point of SUMPRODUCT(). That multiplication sign is unnecessary. It's a bit like using =SUM(A1+A2) (which will also work) instead of =SUM(A1;A2) or just =A1+A2.

Try =SUMPRODUCT(YEAR(A1:A50)=2011;B1:B50="ZZZ") .

Brian Barker