Google Sheets: Compare two dates by Year and count if same year

user_17573054 0
Viewed 92

I have the following data in google sheets (or excel). I want to compare the dates in column B and C to determine if they occurred in the same year, without changing the format of the dates or adding the example column D below. Then, if they did occur in the same year, I want to count how many for that specific year.

A   B           C            D
1   7/7/2020    11/18/2021  (No)
2   8/25/2021   11/17/2021  (Yes)
3   5/27/2021   10/27/2021  (Yes)
4   12/8/2020   10/27/2021  (No)
5   3/27/2019   10/18/2021  (No)
6   1/25/2018   6/30/2018   (Yes)

What I am trying to show/count: Year - Count 2021 - 2 2020 - 0 2019 - 0 2018 - 1

Thank you in advance for your help!

countif google-sheets
2 Answers

Try

=query(ARRAYFORMULA({year(A1:A6),if(year(A1:A6)=year(B1:B6),1,0)}),"select Col1,sum(Col2) group by Col1")

enter image description here

If dates are in text format, use

=query(ARRAYFORMULA(
{REGEXEXTRACT(TO_TEXT(A1:A6),"[0-9]{4}"),if(REGEXEXTRACT(TO_TEXT(A1:A6),"[0-9]{4}")=REGEXEXTRACT(TO_TEXT(B1:B6),"[0-9]{4}"),1,0)}),
"select Col1,sum(Col2) group by Col1")

enter image description here

user_150193800

try:

=INDEX(IFNA(REGEXEXTRACT(TO_TEXT(B1:B), "\d{4}")=REGEXEXTRACT(TO_TEXT(C1:C), "\d{4}")))

enter image description here

and then:

=INDEX(QUERY(IFNA({
 REGEXEXTRACT(TO_TEXT(B1:B), "\d{4}"), ( 
 REGEXEXTRACT(TO_TEXT(B1:B), "\d{4}")=
 REGEXEXTRACT(TO_TEXT(C1:C), "\d{4}"))*1}), 
 "select Col1,sum(Col2) where Col1 is not null 
  group by Col1 label sum(Col2)''"))

enter image description here

user_56326290
Related