# Counting chunks within a row

Copper Contributor

# 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.

EG:

For the row below I would want to gave something return telling me 3 x, 6 blank, 1 x, 3 blank, 5 x, 7 blank, 1 x, 4 blank, 3 x.

Thank you!!

6 Replies

# Re: Counting chunks within a row

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))
)

# Re: Counting chunks within a row

@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.

# Re: Counting chunks within a row

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

# Re: Counting chunks within a row

Thank you, Part of the reason I am on here is I am not super familiar with this stuff. I did copy and paste it but unless I am to put this into a VBA module, I am unclear why the formula is on 12 rows?

# Re: Counting chunks within a row

Also, I did not specify but no, it is B2-V2. I missed that part of the original message.

# Re: Counting chunks within a row

The formula has just been posted with linebreaks to aid readability; it's not intended to be on 12 lines. After you've copied it, make sure you click into the actual cell before pasting, or else paste it into the formula bar at the top. Again, suggest we use the test data you supplied and assume it is in A1:AG1 for now; we can then modify ranges after you're happy that it's working for that test case.