Forum Discussion

chawkie's avatar
chawkie
Copper Contributor
Jul 07, 2023

Analysing a list with multiple text

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,...
BI & Data Analysis
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 07, 2023

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

Resources