Applying a running count to the output from a array directly within the formula that produces the array

[ok_lets]

(One more before I quit for the day and what a gloomy day here in England)

I am counting the occurrences of items on a sheet and outputting by counts each day:

countif of unique items by date:
=COUNTIFS(Sheet22!C:C,B5#,Sheet22!A:A,Sheet21!E4:Sheet21!I4)
- b5# is list of unique items on Sheet22 column c
- sheet 21 e4:i4 is a row of dates.
- range c5:c22 is a static starting balance for each item in b5:b22

The formula above ouputs the count correctly but what I want is the rolling count i.e.
jackdaw starts at 5 and there are in the example none for each so for each date: 5, 5, 5, etc rather than 0,0,0,etc
Further down coat has 44 starting and rolling count would be: 44, 43, 43, 43, etc rather than 0,1,0,0,etc

I would like to wrap the formula above in another formula to achieve this byrow seemed most obvious but doesn't work ???

Seems tricky - I am fairly new to O365 and dynamic functions - but that's no excuse - I am determined to learn

Thanks in advance and thanks for looking

test.xlsm
	B	C	D	E	F	G	H	I
4		new statring balance		31.01.2025	02/02/25	04/02/25	05/02/25	06/02/25
5	jackdaw	5	0	0	0	0	0	0
6	orange	7	0	0	0	0	0	0
7	lemon	8	0	0	0	0	0	0
8	coat	44	1	0	1	0	0	0
9	jockdaw	1	0	0	0	0	0	0
10	tree	3	0	0	0	0	0	0
11	hat	99	0	0	0	0	0	0
12	treeflower	11	0	0	0	0	0	0
13	flower	14	0	0	0	0	0	0
14	river	63	0	0	0	0	0	0
15	green albatross	12	0	0	0	0	0	0
16	garage	14	0	0	0	0	0	0
17	shoes	32	0	0	0	0	0	0
18	yellow blackbird	7	0	0	0	0	0	0
19	alfa romeo	18	2	0	1	1	0	0
20	belt	1	1	0	1	0	0	0
21	red robin	1	0	0	0	0	0	0
22	house	7	0	0	0	0	0	0
Sheet21

Cell Formulas
Range	Formula
E4	E4	=B3
F4	F4	=E4+2
G4	G4	=E4+4
H4:I4	H4	=G4+1
B5:B22	B5	=DROP(UNIQUE(Sheet22!C:C,,FALSE),-1)
D5:D22	D5	=BYROW(E5#,LAMBDA(row,SUM(row)))
E5:I22	E5	=COUNTIFS(Sheet22!C:C,B5#,Sheet22!A:A,Sheet21!E4:Sheet21!I4)
Dynamic array formulas.

 

[StephenCrump]

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4			Start		31 Jan 2025	2 Feb 2025	4 Feb 2025	5 Feb 2025	6 Feb 2025
5		jackdaw	5		5	5	5	5	5		0	0	0	0	0		jackdaw
6		orange	7		7	7	7	7	7		0	0	0	0	0		orange
7		lemon	8		8	8	8	8	8		0	0	0	0	0		lemon
8		coat	44		44	43	43	43	43		0	1	0	0	0		coat
9		jockdaw	1		1	1	1	1	1		0	0	0	0	0		jockdaw
10		tree	3		3	3	3	3	3		0	0	0	0	0		tree
11		hat	99		99	99	99	99	99		0	0	0	0	0		hat
12		treeflower	11		11	11	11	11	11		0	0	0	0	0		treeflower
13		flower	14		14	14	14	14	14		0	0	0	0	0		flower
14		river	63		63	63	63	63	63		0	0	0	0	0		river
15		green albatross	12		12	12	12	12	12		0	0	0	0	0		green albatross
16		garage	14		14	14	14	14	14		0	0	0	0	0		garage
17		shoes	32		32	32	32	32	32		0	0	0	0	0		shoes
18		yellow blackbird	7		7	7	7	7	7		0	0	0	0	0		yellow blackbird
19		alfa romeo	18		18	17	16	16	16		0	1	1	0	0		alfa romeo
20		belt	1		1	0	0	0	0		0	1	0	0	0		belt
21		red robin	1		1	1	1	1	1		0	0	0	0	0		red robin
22		house	7		7	7	7	7	7		0	0	0	0	0		house
23
Sheet1

Cell Formulas
Range	Formula
B5:B22	B5	=Q5:Q22
E5:I22	E5	=LET(a,K5:O22,C5:C22-MAKEARRAY(ROWS(a),COLUMNS(a),LAMBDA(r,c,SUM(INDEX(a,r,SEQUENCE(c))))))
Dynamic array formulas.

You can replace my reference K5:O22 with your COUNTIFS formula.

 

[ok_lets]

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4			Start		31 Jan 2025	2 Feb 2025	4 Feb 2025	5 Feb 2025	6 Feb 2025
5		jackdaw	5		5	5	5	5	5		0	0	0	0	0		jackdaw
6		orange	7		7	7	7	7	7		0	0	0	0	0		orange
7		lemon	8		8	8	8	8	8		0	0	0	0	0		lemon
8		coat	44		44	43	43	43	43		0	1	0	0	0		coat
9		jockdaw	1		1	1	1	1	1		0	0	0	0	0		jockdaw
10		tree	3		3	3	3	3	3		0	0	0	0	0		tree
11		hat	99		99	99	99	99	99		0	0	0	0	0		hat
12		treeflower	11		11	11	11	11	11		0	0	0	0	0		treeflower
13		flower	14		14	14	14	14	14		0	0	0	0	0		flower
14		river	63		63	63	63	63	63		0	0	0	0	0		river
15		green albatross	12		12	12	12	12	12		0	0	0	0	0		green albatross
16		garage	14		14	14	14	14	14		0	0	0	0	0		garage
17		shoes	32		32	32	32	32	32		0	0	0	0	0		shoes
18		yellow blackbird	7		7	7	7	7	7		0	0	0	0	0		yellow blackbird
19		alfa romeo	18		18	17	16	16	16		0	1	1	0	0		alfa romeo
20		belt	1		1	0	0	0	0		0	1	0	0	0		belt
21		red robin	1		1	1	1	1	1		0	0	0	0	0		red robin
22		house	7		7	7	7	7	7		0	0	0	0	0		house
23
Sheet1

Cell Formulas
Range	Formula
B5:B22	B5	=Q5:Q22
E5:I22	E5	=LET(a,K5:O22,C5:C22-MAKEARRAY(ROWS(a),COLUMNS(a),LAMBDA(r,c,SUM(INDEX(a,r,SEQUENCE(c))))))
Dynamic array formulas.

You can replace my reference K5:O22 with your COUNTIFS formula.

Brill, Stephen, brill. I delayed until I had time to have a good long look, at first I didn't quite get it completely - but do now. Is it considered bad form on this forum to ask for an explanation of an offered solution - I'm keen to fully to get to grips with the new features of O365 - dynamic arrays, spilling, let and lambda etc and am very much a novice at the moment so while the solution alone is welcome (though a lot of what I do is experimentation with the new features) a good understanding of the solution is better.

Many thanks (must sat so far I like this forum)

 

[StephenCrump]

Is it considered bad form on this forum to ask for an explanation ...

Not at all. It sounds like you've figured it out, but please feel free to engage with any follow up questions or discussion.

 

[djclements]

Another possibility using SCAN:

=DROP(SCAN(,HSTACK(IFS({0,1},B5#:C5),COUNTIFS(Sheet22!C1:C10000,B5#,Sheet22!A1:A10000,E4:I4)),LAMBDA(a,v,IF(ISNA(a),v,a-v))),,2)

IFS({0,1},B5#:C5) returns a 2-column array: the first column is filled with #N/A errors (which is the default result for FALSE or 0 when only a single logical_test argument is provided for the IFS function); the second column contains the starting balances from column C. HSTACK appends this 2-column array to the left of the COUNTIFS results, and SCAN iterates through the entire array in row major order (left-to-right, top-to-bottom). The #N/A errors are used as the reset point for each row in the array: IF(ISNA(a),v,a-v). Lastly, DROP removes the first 2 helper columns from the final output.

Note: when using a static array such as {0,1}, the row/column separators may differ, depending on your Regional and Language Settings (locale). On my system, comma ( , ) is the column separator and semi-colon ( ; ) is the row separator. You can also replace {0,1} with HSTACK(0,1) if it's giving you grief.

Also: for performance reasons, it's not a good idea to use entire column references with functions like COUNTIFS, SUMIFS, etc., which is why I only referenced the first 10,000 rows in this example. Adjust the number of rows for each range reference to adequately accommodate the size of your dataset.