Search code examples
dategoogle-sheets

Count the rows comparing the dates of 2 cells

I have an google sheets spreadsheet full of book data. Cells that matter are: S = "read" or "abandoned-dnf" status O - date read in the form of mm/dd/yyyy M = Publication year of the book, in the form of yyyy

I want to do a COUNTIF based on whether the read/dnf book (cell S), was read (cell O) in the same year (or earlier, in the case of advanced reader copies) as it was published (cell M).

What I have isn't working: (just read books):

=COUNTIFS(GRData!$S:$S, "read", GRData!$O:$O, "<="&YEAR(GRData!$M:$M))

It just returns 0.

(read or DNF books)

=COUNTIFS(GRData!S:S, {"read", "abandoned-dnf"}, GRData!$O:$O, "<="&YEAR(GRData!$M:$M))

The second one I have (read books that were not read in the same year as it was published) does seem to work:

=COUNTIFS(GRData!S:S, {"read", "abandoned-dnf"}, GRData!$O:$O, ">"&YEAR(GRData!$M:$M), GRData!$M:$M, "<"&YEAR(TODAY()))
Solution

  • Each criteria specified in countifs() should be a value rather than a range of values.

    Since column GRData!O2:O contains dates such as 2023-11-14, you should apply year() to that column instead of column GRData!M2:M that contains integers such as 2023.

    To compare the value in each row to another value in a different column in a row-by-row fashion, use filter(), like this:

    =counta(iferror(filter( 
  GRData!S2:S, 
  regexmatch(GRData!S2:S, "(?i)read|abandoned-dnf"), 
  year(GRData!O2:O) <= GRData!M2:M 
)))

    See filter() and regexmatch().

    The question specifies the tag. See Working with date and time values in Google Sheets.