Using SPILL Array in Countif

[Jeffrey Mahoney]

I'm trying to count the number of dates that fall into another set of dates. I could create a series of if statements to get the job done, but I'm trying to create and learn something new.

I want to use Countif with Sequence. It does work:
=SUM(COUNTIF(L3#,J11#))
Cell J11: =SEQUENCE(,I8-H8,H8,1)
Cell L3: =SEQUENCE(,M4-L4,L4,1)

I get the correct return if the Sequence SPILL is not overlapped by any other cells. How do I turn the Sequence SPILL into an Array that Countif can use?

Jeff

 

[awoohaw]

what is in cells L4 thru M4, and I8 thru H8?

 

[awoohaw]

I just made a guess as to what your date ranges are.
But, try this:

Mr excel questions 58.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		2
2
3					2023-09-01	2023-09-02	2023-09-03	2023-09-04	2023-09-05	2023-09-06	2023-09-07	2023-09-08	2023-09-09	2023-09-10	2023-09-11
4					2023-09-01	2023-09-12
5
6
7
8	2023-09-10	2023-09-15
9
10
11			2023/09/10	2023/09/11	2023/09/12	2023/09/13	2023/09/14
Jeffrey Mahoney

Cell Formulas
Range	Formula
I1	I1	=SUM(--NOT(ISNA(MATCH(J11#,L3#,0))))
L3:V3	L3	=SEQUENCE(,M4-L4,L4,1)
J11:N11	J11	=SEQUENCE(,I8-H8,H8,1)
Dynamic array formulas.

 

[Jeffrey Mahoney]

Here is my range. I need to convey to what exactly the need is. The formulas I gave above worked, but only if the SPILL array wasn't blocked. If the formulas with Sequence are blocked to the right, it doesn't work.

I need to find a way to convert the sequence array from a SPILL result to an ARRAY result that I can use in the Countif.

I tried several things: TextSplit, TextJoin, ArraytoText

Countif and Sequence.xlsm
	G	H	I	J	K	L	M	N
4	Task Duration	Start Date	Due Date		8/14/2023	8/21/2023	8/28/2023	9/4/2023
5	3	8/21/2023	8/24/2023	#SPILL!		X
6	1	8/23/2023	8/24/2023			X
7	10	8/21/2023	8/31/2023			X	X
8	20	8/26/2023	9/15/2023	#SPILL!		2
9		45164	45184
10
11				45164	###	45166	45167	45168
Sheet16

Cell Formulas
Range	Formula
L4:N4	M4	=L4+7
K5:K10,M9:N10,L5:N7	M5	=IF(OR(AND($H5>=M$4,$H5<M$4+7),AND($I5>=M$4,$I5<M$4+7)),"X","")
J5	J5	=SEQUENCE(,I5-H5,H2,1)
H5:H8	H5	=I5-G5
J8,J11:AC11	J8	=SEQUENCE(,I5-H5,H5,1)
L8	L8	=SUM(COUNTIF(L3#,J11#))
H9:I9	H9	=H8

 

[awoohaw]

I dont understand your worksheet at all. But, I think you are asking for a formula in cell L8. But, I really cannot tell.
This is what I have come up with: (I had to change some of the values in the column G to get an value.

Mr excel questions 58.xlsm
	G	H	I	J	K	L	M	N
1
2
3
4	Task Duration	Start Date	Due Date		2023/08/14	2023/08/21	2023/08/28	2023/09/04
5	10	2023-08-14	2023-08-24		X	X
6	1	2023-08-23	2023-08-24			X
7	10	2023-08-21	2023-08-31			X	X
8	25	2023-08-21	2023-09-15			3
9		2023-08-21	2023-09-15
Jeffrey Mahoney

Cell Formulas
Range	Formula
L4:N4	M4	=L4+7
M9:N9,K5:K9,L5:N7	M5	=IF(OR(AND($H5>=M$4,$H5<M$4+7),AND($I5>=M$4,$I5<M$4+7)),"X","")
H5:H8	H5	=I5-G5
L8	L8	=SUM(--NOT(ISNA(MATCH(SEQUENCE(,I5-H5,H5,1),SEQUENCE(,I8-H8,H8,1),0))))
H9:I9	H9	=H8