In Excel, how can I count the number of instances of a string in a column

I have a column with different staff members who have attended an event on different dates. Sometimes it is only one person attending, sometimes it is multiple. I want to be able to calculate how many times each individual staff member has attended. I have tried COUNTIF(), but that won't pick up the occasions when more than one person has attended.

Staff attending
Ben
John
Dave
Mark
Ben, John
Mark, John
Ben & Dave
Mark & John

In the above table I want to be able to calculate how many times each individual staff member has attended.

I apologise in advance, I'm sure this question has been asked and answered on occasions, but I probably lack awareness of the syntax and how to verbalise my query.

Solution

Assuming there is no Excel Constraints as per the tags posted, then one could use GROUPBY() presently available in MS365 Beta Channel then the following should work.

=LET(
     _StaffAttending, TOCOL(TEXTSPLIT(TEXTAFTER(", "&SUBSTITUTE(A2:A9," &",","),", ",SEQUENCE(,10)),", "),2),
     GROUPBY(_StaffAttending,_StaffAttending,COUNTA,0,0))

To make things more clearer, the above formula can be expanded to few more steps:

=LET(
     _SameDelimiters, SUBSTITUTE(A2:A9," &",","),
     _CountDelimiters, LEN(_SameDelimiters)-LEN(SUBSTITUTE(_SameDelimiters,", ",)),
     _Instance_Num, SEQUENCE(,MAX(_CountDelimiters)),
     _SplitByRow, TOCOL(TEXTSPLIT(TEXTAFTER(", "&_SameDelimiters,", ",_Instance_Num),", "),2),
     _Output, GROUPBY(_SplitByRow,_SplitByRow,COUNTA,0,0),
     _Output)

Or, Using MMULT() with same steps applied like above except it uses MMULT() to get the number counts by doing matrix multiplication of two arrays.

=LET(
     _StaffAttending, TOCOL(TEXTSPLIT(TEXTAFTER(", "&SUBSTITUTE(A2:A9," &",","),", ",SEQUENCE(,10)),", "),2),
     UNIQUE(HSTACK(_StaffAttending, MMULT(N(_StaffAttending=TOROW(_StaffAttending)),SEQUENCE(ROWS(_StaffAttending),,,0)))))

Or, Using POWER QUERY will be more apt here as it can handle quite a lot amount of datasets, so to achieve this using the said procedure, follow these simple steps using POWER QUERY window UI:

First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_1

Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source," &",",",Replacer.ReplaceText,{"Staff attending"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Staff attending", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Staff attending"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Staff attending", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Staff attending"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

However, one could also accomplish this using COUNTIFS() or COUNTIF() using wildcard operator * if the staffs names are listed manually, then

=COUNTIF($A$2:$A$9,"*"&C2:C5&"*")

To list unique names of the staffs those attended, can use:

=UNIQUE(TOCOL(TEXTSPLIT(TEXTAFTER(", "&SUBSTITUTE(A2:A9," &",","),", ",SEQUENCE(,10)),", "),3))

All the above solutions posted are without using any LAMBDA() iterations as well don't use TEXTJOIN() function and the reason to avoid the latter specifically as there are character limitations for the said function if it exceeds the limit will render #VALUE! error. And we are not aware of how large the dataset is as well as whether the dataset has full names or first names only. However, posting as an alternative:

=LET(
     _Split, TEXTSPLIT(TEXTJOIN("|",,A2:A9),,{"|",", "," & "}),
     GROUPBY(_Split, _Split, COUNTA,,0,))

Or,

=LET(
     _Split, TEXTSPLIT(TEXTJOIN("|",,A2:A9),,{"|",", "," & "}),
     PIVOTBY(_Split,, _Split, COUNTA,,0,))

Or,

=LET(
     _Split, TEXTSPLIT(TEXTJOIN("|",,A2:A9),,{"|",", "," & "}),
     UNIQUE(HSTACK(_Split,MMULT(N(_Split=TOROW(_Split)),SEQUENCE(ROWS(_Split),,,0)))))