Excel find and count unique values in 2 dimensional array

Question

I have arrays of excel data 3 cols x 300 rows - example

A	B	C
UNIT_TESTING	REMOTE_TEAM_AVATARS	SOCIAL_TIME
SOCIAL_TIME	ELIMINATE_LONG_LIVED_FEATURE_BRANCHES

There will be blanks in some rows.

My goal among the 900 individual cells, find the unique values. Once I have the values displayed I want to count how many instances there of each unique value.

In the trivial case above the result would be:

A	B
SOCIAL_TIME	2
ELIMINATE_LONG_LIVED_FEATURE_BRANCHES	1
...

In an ideal world I want to avoid creating a mid calc column of 900 elements

Answer 1

I know this is a little old, but after searching for an answer to this, I managed to come up with my own solution that I think may be simpler.

I used nested Textjoin and Textsplit and then took Unique values of the resultant array. And sorted for good measure. In cell E1

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(";",TRUE,A1:C2),,";")))

Then in cell F1 I used a Countif on the spill range from E1

=COUNTIF(A1:C2,E1#)

Answer 2

With Office 365 we can use UNIQUE, FILTER and SEQUENCE to get the desired output:

=LET(
    rng,    A1:C2,
    clm,    COLUMNS(rng),
    ct,     ROWS(rng)*clm,
    arr,    INDEX(rng,INT(SEQUENCE(ct,,1,1/clm)),MOD(SEQUENCE(ct,,0),clm)+1),
    flt,    FILTER(arr,arr<>""),
    unq,    UNIQUE(flt),
    SORT(CHOOSE({1,2},unq,COUNTIF(rng,unq)),{2,1},{-1,1}))