Count occurrences of words from multiple columns

Question

I have a spreadsheet like this, where the values A-E are the same options coming from a form:

+------+------+------+
| Opt1 | Opt2 | Opt3 |
+------+------+------+
|   A  |   A  |   B  |
|   B  |   C  |   A  |
|   C  |   C  |   B  |
|   A  |   E  |   C  |
|   D  |   B  |   E  |
|   B  |   E  |   D  |
+------+------+------+

I want to make a ranking, showing the most chosen options for each option. I already have this, where Rank is the ranking of the option and number is the count of the option:

+------+------+------+
| Rank | Opt1 | Numb |
+------+------+------+
|   1  |   A  |   2  |
|   1  |   B  |   2  |
|   3  |   C  |   1  |
|   3  |   D  |   1  |
+------+------+------+ (I have 3 of these, one for each option)

I want to do now a summary of the 3 options, making the same ranking but joining the options. It would be something like:

+------+------+------+
| Rank |Opt123| Numb |
+------+------+------+
|   1  |   B  |   5  |
|   2  |   A  |   4  |
|   2  |   C  |   4  |
|   4  |   E  |   3  |
|   5  |   D  |   2  |
+------+------+------+

The easiest way to do this would be getting the data from the three ranking tables or from the original three data columns?

And how would I do this?

I already have the formula to get the names of the options, the count and ranking, but I don't know how to make them work with multiple columns.

What I have (the F column is one of the data columns):

Column B on another sheet:

=SORT(UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F)))); RANK(COUNTIF('Form Responses'!F2:F; UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F))))); COUNTIF('Form Responses'!F2:F; UNIQUE(FILTER('Form Responses'!F2:F;NOT(ISBLANK('Form Responses'!F2:F))))); TRUE); FALSE)

Column C:

=ArrayFormula(COUNTIF('Form Responses'!F2:F; FILTER(B2:B;NOT(ISBLANK(B2:B)))))

Column A:

=ARRAYFORMULA(SORT(RANK(FILTER(C2:C;NOT(ISBLANK(C2:C))); FILTER(C2:C;NOT(ISBLANK(C2:C))))))

Answer 1

Edited:

Merge cols:

=TRANSPOSE(split(join(",",D2:D,E2:E),","))

merges 2 cols, not very clean, but works. (Same as here Stacking multiple columns on to one?)

Full formula:

=SORT(UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),",")))))); RANK(COUNTIF(TRANSPOSE(split(join(",",D2:D,E2:E),",")); UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),","))))))); COUNTIF(TRANSPOSE(split(join(",",D2:D,E2:E),",")); UNIQUE(FILTER(TRANSPOSE(split(join(",",D2:D,E2:E),","));NOT(ISBLANK(TRANSPOSE(split(join(",",D2:D,E2:E),","))))))); TRUE); FALSE)

The transpose could be done after the sort.