Count rows using 2 same criteria points in 1 range OR another

NTDSC

Hello! Struggling with formula/function syntax... I'm trying to get a count of projects that fall within a calendar year (i.e. had an active contract at any point during the year) using the start & end date of their contracts. Essentially, if the contract start date OR the contract end date is on or after 1/1 and on or before 12/31, it falls within that year. So the criteria would be EITHER ({Contract Start Date} >= 1/1/23 AND {Contract Start Date} <= 12/31/23) OR ({Contract End Date} >= 1/1/23 AND {Contract End Date} <= 12/31/23) .... But I can't figure out how to get that in a formula.

The following is basically half of what I need:

=COUNTIF({Contract Start}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))

This just gets me just the ones that started within 2023. Using the same exact formula but with Contract End as the reference would get the ones that ended within 2023.

But I can't start COUNTIF with "OR" so that it's looking at Start OR End... and I can't use OR on the criteria part of the formula because that's where I need AND (it's 2 different ranges but the same criteria for both)... and I can't start the whole formula with OR to do two different COUNTIF functions, since OR has to be nested within another function... And COUNTIFS would only get me the ones that started AND ended in 2023. I tried combining COUNT with COLLECT but that didn't work either, even when I changed the range (I thought maybe it didn't want to collect dates for a count so I changed it to look at a text field instead).

Thanks in advance for your help!

Leslie

NTDSC

Update: I figured out a solution -- count the ones that start in 2023, the ones that end in 2023, and then subtract the ones that start AND end in 2023 so they're not double counted:

=(COUNTIF({Contract Start}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))) + (COUNTIF({Contract End}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))) - COUNTIFS({Contract Start}, @cell >= DATE(2023, 1, 1), {Contract Start}, @cell <= DATE(2023, 12, 31), {Contract End}, @cell >= DATE(2023, 1, 1), {Contract End}, @cell >= DATE(2023, 12, 31))

Austin Smith

@NTDSC

I advise helper rows maybe a bit too much on here, but...

Option 1: gives you a bit more data to play with, if that's helpful

Helper column 1 = year(contract start date@row) helper column 2 = year(contract end date@row)

helper column 3 = if(helper col 1=2023, 2023, if(helper col 2=2023, 2023, year(contract end date)))

Option 2: cleaner, smaller version of the same thing

Helper column =if(year(start date)=2023, 2023, if(year(end date)=2023, 2023, year(end date)))

Then for both options, just count the 2023s in the helper column. You would have to go in and change the formula every year unless you made it more robust, or made it depend on summary data fields.

NTDSC

Update: I figured out a solution -- count the ones that start in 2023, the ones that end in 2023, and then subtract the ones that start AND end in 2023 so they're not double counted:

=(COUNTIF({Contract Start}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))) + (COUNTIF({Contract End}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))) - COUNTIFS({Contract Start}, @cell >= DATE(2023, 1, 1), {Contract Start}, @cell <= DATE(2023, 12, 31), {Contract End}, @cell >= DATE(2023, 1, 1), {Contract End}, @cell >= DATE(2023, 12, 31))

NTDSC

@Austin Smith Just saw your comment - thank you so much!

Austin Smith

@NTDSC

NP. Nice fix!