Analysing a list with multiple text

[chawkie]

Looking for some help please...

I have a list similar to below where people have sorted a list in an order.

I'd like to count the amount of times the word "A" comes first in all fields, then count the amount of times it appears 2nd etc.

Is there a way of doing this please?...

[#2]

@chawkie in excel 365 we have textsplit and textjoin and lambda helper functions so try this:

=LET(in,X1:X3, grid,TEXTSPLIT(TEXTJOIN(",",,in),";",","),BYCOL(grid,LAMBDA(c,SUM(IF(c="a",1,0)))))

[#3]

@chawkie 

As variant

=MMULT(
    TRANSPOSE(ROW(A1:A3) / ROW(A1:A3)),
    --(
        FILTERXML(
            "<t><s>" & SUBSTITUTE(A1:A3, ";", "</s><s>") &
                "</s></t>",
            "//s[" & TRANSPOSE(ROW(1:4)) & "]"
        ) = "a"
    )
)

for