Count days between to Table Ranges that falls in SLA intervals

[Nicha]

Hi Mr. Excel members,

Considering the Projects Table below, I need to count the number of projects whose difference, in calendar days, falls within the SLA Table intervals, without having to use the [Days] Column as support. The [Days] calculation is considering two premises:

1. If [End_Date] is not Valid Date (i.e. it is < 0 or empty), consider Today's date (Today()).
2. the calculation includes the limits of the intervals; therefore, if [Start_Date] equals [End_Date], the result must be 1; (hence adding 1 [+1] in the formula).

Posts.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2	Count days between to Table Ranges that falls in SLA intervals
3
4				When not valid End_Date consider Today()			SLA's Board - hand filled
5				being the same date, it returns 1 day			<=1	>1 and <=3	>3 and <=5	>5
6	Status	Start_Date	End_Date	Days			D	D+3	D+5	D>5	Total
7	Pending	2024-09-02		29		Pending	0	1	0	3	4
8	Pending	2024-09-29		2		Completed	1	2	1	2	6
9	Completed	2024-09-05	2024-09-06	2							10
10	Pending	2024-09-20		11
11	Completed	2024-09-12	2024-09-12	1		With Formulas	D	D+3	D+5	D>5	Total
12	Completed	2024-09-16	2024-09-26	11		Pending	0	1	0	3	4
13	Pending	2024-09-20		11		Completed	1	2	1	2	6
14	Completed	2024-09-09	2024-09-15	7							10
15	Completed	2024-09-11	2024-09-14	4
16	Completed	2024-09-15	2024-09-16	2
Sheet1

Cell Formulas
Range	Formula
L12:L13,L7:L8	L7	=SUM(H7:K7)
L9,L14	L9	=SUM(L7:L8)
H12	H12	=COUNTIFS(Table1[Status],"Pending",Table1[Days],"<=1")
I12	I12	=COUNTIFS(Table1[Status],"Pending",Table1[Days],">1",Table1[Days],"<=3")
J12	J12	=COUNTIFS(Table1[Status],"Pending",Table1[Days],">3",Table1[Days],"<=5")
K12	K12	=COUNTIFS(Table1[Status],"Pending",Table1[Days],">5")
H13	H13	=COUNTIFS(Table1[Status],"Completed",Table1[Days],"<=1")
I13	I13	=COUNTIFS(Table1[Status],"Completed",Table1[Days],">1",Table1[Days],"<=3")
J13	J13	=COUNTIFS(Table1[Status],"Completed",Table1[Days],">3",Table1[Days],"<=5")
K13	K13	=COUNTIFS(Table1[Status],"Completed",Table1[Days],">5")
E7:E16	E7	=IF(OR(DAYS(D7,C7)<0,NOT(ISNUMBER([@[End_Date]]))),DAYS(TODAY(),C7)+1,DAYS(D7,C7)+1)

I need to get the formulas from [H12 to K13] to be able to perform the calculation without the help of the [Days] column of the Projects table.

Thank you in advance.

 

[myall_blues]

Does this work for your version of Excel? It should but I'm not sure if the dynamic arrays in 365 are giving me a false sense of security!
Note the formulas are array formulas and need to be entered with CTRL+SHIFT+ENTER.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2		Count days between to Table Ranges that falls in SLA intervals
3
4							SLA's Board - hand filled
5							<=1	>1 and <=3	>3 and <=5	>5
6		Status	Start_Date	End_Date			D	D+3	D+5	D>5	Total
7		Pending	3/09/2024			Pending	0	1	0	3	4
8		Pending	30/09/2024			Completed	1	2	1	2	6
9		Completed	5/09/2024	6/09/2024							10
10		Pending	21/09/2024
11		Completed	13/09/2024	13/09/2024		With Formulas	D	D+3	D+5	D>5	Total
12		Completed	17/09/2024	27/09/2024		Pending	0	1	0	3	4
13		Pending	21/09/2024			Completed	1	2	1	2	6
14		Completed	10/09/2024	16/09/2024							10
15		Completed	12/09/2024	15/09/2024
16		Completed	16/09/2024	17/09/2024
Sheet13

Cell Formulas
Range	Formula
K7:K8,K12:K13	K7	=SUM(G7:J7)
K9,K14	K9	=SUM(K7:K8)
G12:G13	G12	=SUM((Table1[Status]=$F12)*((IF(Table1[End_Date]="",TODAY(),Table1[End_Date])-Table1[Start_Date]+1)<=1))
H12:H13	H12	=SUM((Table1[Status]=$F12)*((IF(Table1[End_Date]="",TODAY(),Table1[End_Date])-Table1[Start_Date]+1)>1)*((IF(Table1[End_Date]="",TODAY(),Table1[End_Date])-Table1[Start_Date]+1)<=3))
I12:I13	I12	=SUM((Table1[Status]=$F12)*((IF(Table1[End_Date]="",TODAY(),Table1[End_Date])-Table1[Start_Date]+1)>3)*((IF(Table1[End_Date]="",TODAY(),Table1[End_Date])-Table1[Start_Date]+1)<=5))
J12:J13	J12	=SUM((Table1[Status]=$F12)*((IF(Table1[End_Date]="",TODAY(),Table1[End_Date])-Table1[Start_Date]+1)>5))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nicha]

Hi @myall_blues, thank you very much.
Your "Array" formulas work very well on my Excel 2016.

I know that opting to use a solution through the CountIFs function, would probably be much more complex. But do you think that using SUMPRODUCT() would also work? If so; could you show me, please, how the formulas in [G12] and [H12] would be?

Thank you once more.

 

[myall_blues]

There might be a way but I wasn’t able to figure it out. I started out trying to use SUMPRODUCT as it is my normal goto for earlier versions, but in this case I couldn’t work out how to implement the IF function inside the SUMPRODUCT and had to go back to a normal array formula.

 

[Nicha]

There might be a way but I wasn’t able to figure it out. I started out trying to use SUMPRODUCT as it is my normal goto for earlier versions, but in this case I couldn’t work out how to implement the IF function inside the SUMPRODUCT and had to go back to a normal array formula.

Ok. My best to you.

I'll wait for someone to presente some other Aproach. I'm saying that because the Main Table has more than 40.000 lines. But if no one will not another proposal I'll close with your's.

My best regards @myall_blues