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.
=D2-C2
)?