Count Cells Within Date Ranges Comparing Two Variable Dates

For the sake of simplicity, I'm trying tally the difference between two variable dates into several timeframe categories. I'll provide some sample data:

   A        B           C           D
1  Account# Name        Start Date  End Date   
2  24621    Customer1   11/17/21    11/17/2021
3  24621    Customer2   09/17/21    11/17/2021
4  24621    Customer3   06/17/21    11/17/2021
5  24621    Customer4   03/17/21    11/17/2021
6  24521    Customer5   11/17/20    11/17/2021
7  24321    Customer5   10/17/20    11/17/2021

I'd then like to tally the difference between start and end dates into the following categories (as they would appear in a separate table):

Total   Time Period
1       < 1 Month
1       < 3 Months
1       < 6 Months
1       < 1 Year
2       >= 1 Year

I've tried using =SUMIFS(C2:C7, D2:D7, (D2-C2)<=31) and =COUNTIF(C2:C7, (D2-C2)<=31) but both return values of 0. Subtracting the dates returns the correct number of days between, so I feel like I should be able to tally the values between 0 and 31, 32 and 93, etc without needing to add a helper column to run a count forumla on.

I've also used =SUMPRODUCT(--(C:C<>D:D))-1 to count the number of cells that have the same or different dates listed--I doubt it applies much to this problem, but I've fiddled around with these formulas for several hours today and I'm not getting anywhere.

EDIT: Extra info for troubleshooting help. Had to sanitize the data a little bit, but I believe all the info needed is here. In the bottom right are the formulas I'm using; as you'll see, the formula works for the first 17 lines, but applying it to anything over 18 seems to break it. I'm fairly certain everything is transcribed correctly, but am unfamiliar with these formulas (besides Count) so not sure if there's a limit or I have a typo somewhere.

Solution 1:

You can use this formula:

=XLOOKUP(DATEDIF(C2,D2,"M"),{0,3,6,12},{"< 1 Month","1-3 Months","3-6 Months","6 Months - 1 Year"},"> 1 Year",1)

The DATEDIF calculates the months between the dates. The XLOOKUP looks for each month, or the next largest item (indicated by the final parameter), in the first array, then returns the corresponding position from the second array. If the months between the dates cannot be found (i.e. it is larger than 12), then it returns the default value specified in the fourth parameter in XLOOKUP.

EDIT:

As an aside, you may want to change the definition of the third range to be 4-6 Months.

EDIT 2:

Reading your edited post, you can do this:

Type out your row headers for your tally table, then use this formula:

=COUNT(FILTER($A$2:$A$7,XLOOKUP(DATEDIF($C$2:$C$7,$D$2:$D$7,"M"),{0,3,6,12},{"< 1 Month","1-3 Months","4-6 Months","7 Months - 1 Year"},"> 1 Year",1)=$A12))

In this formula, we are filtering column A (could be any column, really), for those rows where the original XLOOKUP formula, adjusted to use ranges in the DATEDIF, returns the value in the adjacent cell. We then count the filtered rows to get the number of rows that would match that criteria.

EDIT 3:

With regards to your issue with A2:A18, the problem arises because the start date on row 18 (Nov 7 2021) is after the end date (June 22 2021).

Because of this, the DATEDIF function is returning a #NUM! error (see cell G18 below). Because of that, the XLOOKUP is also returning a #NUM! error (see cell I18 below). And the FILTER is also returning a #NUM! error (cell L2). The zero is happening because COUNT only counts numbers. It can't count text, or errors. So it returns zero (cell L6). You will see that if you change it to use COUNTA instead of COUNT, it will return 1, because it's counting 1 error.

To get around this, you can either:

1. Ensure the start date is after the end date on every row, OR
2. Have the formula ignore the error row(s) by wrapping the DATEDIF in an IFERROR call.

Like this:

IFERROR(DATEDIF(E2,F2,"M"),-1)

Where we're saying, if DATEDIF returns an error, then return -1 instead of the error. We can then add an element to the XLOOKUP arrays to handle the -1.

Like this:

XLOOKUP(G2,{-1,0,3,6,12},{"Error","< 1 Month","1-3 Months","3-6 Months","6 Months - 1 Year"},"> 1 Year",1)

As you can see, by making these changes, row 18 has -1 in the DATEDIF return value and "Error" as the return value for XLOOKUP.

Your full formula would then be:

=COUNT(FILTER($A$2:$A$7,XLOOKUP(IFERROR(DATEDIF($C$2:$C$7,$D$2:$D$7,"M"),-1),{-1,0,3,6,12},{"Error","< 1 Month","1-3 Months","4-6 Months","7 Months - 1 Year"},"> 1 Year",1)=$A12))