Can you SPILL and COUNTIF?

[MiniFav]

I am looking to count the first instance of data in a column, however when a new row gets inserted the countif needs to be reapplied.
Is there a way to make this spill?

Book1
	A	B
1	DATA	First instance
2	a	1
3	b	1
4	c	1
5	inserted row
6	a	0
7	c	0
8	d	1
9	e	1
10	f	1
Sheet1

Cell Formulas
Range	Formula
B2:B4,B6:B10	B2	=(COUNTIF($A$2:$A2,$A2)=1)+0

I have considered using an IF function prior to force the spill but this results in all returns to be the same.

Any suggestions are most welcome!

 

[Georgiboy]

Have you tried converting your range into a table?

 

[MiniFav]

Sorry I just realised this had been answered in the past.

Below was the solution.

=LET(a,B3:B1000,IF(MMULT((SEQUENCE(ROWS(a))>=SEQUENCE(,ROWS(a)))*(a=TRANSPOSE(a))*(a<>""),SEQUENCE(ROWS(a),,,0))>1,"Ignore",""))

 

[Peter_SSs]

Below was the solution.

=LET(a,B3:B1000,IF(MMULT((SEQUENCE(ROWS(a))>=SEQUENCE(,ROWS(a)))*(a=TRANSPOSE(a))*(a<>""),SEQUENCE(ROWS(a),,,0))>1,"Ignore",""))

That seems somewhat different to what you asked for in post #1 but if it does what you want, then wouldn't this much shorter one one also do what you want?

=LET(d,B3:B1000,r,ROW(d),IF(IFNA(MATCH(d,B:B,0),r)=r,"","Ignore"))