Google Sheets Dynamic Range (for finding instance number) Countif in arrayFormula

1
votes

I find the instance number of a data entry by using:

=countif(C$2:C2,C2)

When filled down, this formula displays the number of times C2,C3,C4,etc. occurs above it, thus giving its "instance number".

I would like to convert this to an array formula (to avoid the need to fill down) but,

=arrayformula(countif(C$2:C2,C2:C))

does not work. I am unsure how to convert the range argument of countif to comply with the arrayformula while keeping this range dynamic.

Example sheet: https://docs.google.com/spreadsheets/d/1cApbO2HdeiaJd7yEkS1oFzxEG5wuM2XHLU7CK_OqRCA/edit#gid=0

2
arraysgoogle-sheetsformulasarray-formulas

2 Answers

1
votes

Adapted a solution from here which ultimately had an unknown source.

=ArrayFormula(if(len(A2:A),index(sort(iferror({sort(row(A2:A),A2:A,1),row(A2:A)-row()+2-vlookup(sort(A2:A),{unique(A2:A),iferror(match(unique(A2:A),sort(A2:A),0))},2,0)})),,2),))
1
votes

A simple way to countif with arrayformula:

=ArrayFormula(COUNTIF(A1:A6,A1:A6))

enter image description here

Tip: use the same range twice