sorting text to reveal occurrence of adjective

Occasional Visitor

This has probably been addressed but I need a quick and simple answer. I want to sort or filter the adjectives to show how many times each is listed and if there are duplicatesscreenshot of looking glass.png

1 Reply


If you call the array of adjectives 'attribute' then

= COUNTIFS(attribute, attribute)

gives a similar dimensioned array with the count of occurrences for each adjective.

= SUM( 1 / COUNTIFS(attribute, attribute) )

is the number of distinct adjectives used in the table.  To go further, a list of distinct options from which the adjectives are drawn would help.

= COUNTIFS(attribute, list)

provides a count of each adjective.  The two column result can be sorted and filtered using

= SORT(FILTER(result, count>0), 2,-1)

The final formula may mean it is time to take a license 365!