J
jumpmaster_france
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 it’s 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
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 it’s 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