Forum Discussion
Counting chunks within a row
Hi! I have a really simple spreadsheet that has X's and empty cells. I know its super simple to count how many times X's appear, however, I want to return results that count each "chunk" of cells...
Assuming those entries are in A1:AG1:
=LET(
α,A1:AG1,
β,INDEX(α,1),
γ,β="",
δ,COLUMN(α),
ε,LAMBDA(κ,λ,
LET(ζ,FREQUENCY(IF(α=κ,δ),IF(α<>κ,δ)),FILTER(ζ,ζ<>0,0)&λ)
),
ω,ε("x"," x"),
φ,ε(""," empty"),
TEXTJOIN(", ",,TOCOL(HSTACK(IF(γ,φ,ω),IF(γ,ω,φ)),2))
)
JosWoolleyThank you however I am not at all sure what that response is telling me? Also, I am going to need to be able to apply this in other rows, so I am unsure how to modify this.
- Why don't you test it on the sample data you supplied, and then go from there? As I said in my first reply, I simply assumed that the data you posted was in the range A1:AG1. So I suggest that you do likewise, and you'll see that my formula gives you the precise results you asked for. Once you've got it working for your sample data, we can look at adapting/extending it to different ranges. But first things first.
Regards- Also, I did not specify but no, it is B2-V2. I missed that part of the original message.
