Forum Discussion

Bethany27's avatar
Bethany27
Copper Contributor
Apr 03, 2023

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

 

Excel
Formulas and Functions
  • JosWoolley's avatar
    JosWoolley
    Iron Contributor
    Apr 03, 2023

    Bethany27 

     

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

     

    • Bethany27's avatar
      Bethany27
      Copper Contributor
      Apr 04, 2023

      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. 

      • JosWoolley's avatar
        JosWoolley
        Iron Contributor
        Apr 04, 2023
        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

Resources