In the table below, the 1^st column is person’s name, and the multiple columns after it are items they purchased. There are people who sometimes buy multiple same items in one purchase and who place multiple orders at different times.

We need to rearrange the table into a crosstab, where the column headers are items and the row headers are people’s names, as shown below:

Use SPL XLL to do this:

=spl("=?.groupc@r(~1;~.m(2:);1).pivot@s(~1:Name; ~2,count(~2))",A1:D5)

groupc@r groups members of a sequence by a specified number and transposes columns to rows; ~1 represents the 1^st child member of the current member, and ~.m(2:) gets child members of the current member from the 2^nd to the last. pivot@s transposes rows to columns and performs aggregation on each group of data.

Source:https://www.reddit.com/r/excel/comments/1ctnd42/trying_to_find_the_total_number_of_times_a_word/