I had a similar previous question but I’m not able to adapt the given solution to this case as an ArrayFormula or ideally a Query Formula.
I did not find a suitable duplicate question or solution.
I need to output the counts of cells as this sample (in column "Item 2"):
| Item 1 | Item 2 |
|---|---|
| 🍀 | 3 |
| 🍀 | 2 |
| 🍀 | 3 |
| 🍀 | 5 |
| 🍀 | 3 |
| 🍀 | 3 |
| 🍀 | 2 |
The counting method must be as counting each cell between 2 cells containing a clover emoji, those cells included.
An annoted screenshot with respective clover groups rectangles if that helps:
I tried adapting the previous solution answer but though it seems to work I’m not sure how to make it a fully fledged ArrayFormula formula or adapt it as a Query Formula:
=ArrayFormula(iferror(if(A2="","",match(true,A3:A<>"",0)+1),1))
Gives this output:
I was not successful at attempting to adapt the ArrayFormula solution.
I would be very interested to learn about a solution to this problem using a Query formula if possible.
>Solution :
Here’s one approach you may test out:
=map(A:A,lambda(Σ,if(Σ<>"🍀",,ifna(xmatch("🍀",offset(Σ,1,,rows(A:A)))+1))))
