I hope that I have been able to make this clear enough

1. First thing I am trying to do. I have a column of cells that have
multiple values, some with text and some with no values at all. I want to be
able to display in A1 the most commonly occurring text in cells C1:C15, and
be able to display in B1 the number of times that A1 occurs in the same
range. Below are the formulas that I am using. There are two problems that I
am running into: First, the formula returns a #NA error if any of the cells
in the range are left blank. Second, the formula counts the spaces or zeros,
so if there are more blanks than the word €œamber€� then A1 returns €œ €� and B1
returns the corresponding number.

A1
=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))

B1
=COUNTIF(C1:C16,A1)

2. Second thing I am trying to do. In A2 I want to display the second most
commonly occurring text in the range, with its corresponding count in cell
B2, and the third most in A3 and B3, etc

Illustration:

C1 Amber
C2 Red
C3
C4
C5
C6 Red

Desired result:

A1 "Red" B1 "2"
A2 "Amber" B2 "1"

Results with forumla as posted

A1 " " B1 "3"

Any help would be greatly appreciated

Thanks in advance