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, then count the amount of times it appears 2nd etc.

 

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

  • mtarler's avatar
    mtarler
    Silver Contributor

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

     

Resources