CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jonathan
Jonathan

Reputation: 641

COUNTIF Year equals Year - Google Sheet

I would like to know how many times a a specific year is found in a column. Format of column C is Date (for example: 15/2/2021). Obviously the code below doesn't work, but to give you an idea:

Example

In the example above the output on B:1 should be: 3

Upvotes: 2

Views: 1442

Answers (2)

player0
player0

Reputation: 1

use:

=INDEX(COUNTIF(YEAR(C:C); A1))

enter image description here

or get whole table with:

=QUERY(C:C; 
 "select year(C),count(C) 
  where C is not null 
  group by year(C) 
  label year(C)'',count(C)''")

enter image description here

Upvotes: 5

brunoff
brunoff

Reputation: 4219

You can use this workaround:

cell B1 formula:

=COUNTIFS(C:C,">="&A1&"-01-01",C:C,"<"&(A1+1)&"-01-01")

And then apply the same formula for the rest of column B cells.

It will count everything which is greater than or equal to the first day of the year and lower than the first day of next year. It is not pretty but it works.

Upvotes: 4

Related Questions