counting within cells
Posted by cj on March 18, 2001 11:08 AM
How can I count the number of instances of
a particular value- say "x" in a cell (a1).
Regards
cj
Posted by Aladin Akyurek on March 18, 2001 11:41 AM
There is a well-known array-formula to do this (see http://www.emailoffice.com/excel/arrays-bobumlas.html).
=SUM(LEN(A1))-SUM(LEN(SUBSTITUTE(A1,"x","")))
You need to hit CONTROL+SHIFT+ENTER at the same time to enter the above formula.
Aladin
Posted by Mark W. on March 19, 2001 6:55 AM
Aladin, I liked your solution; however, this formula
doesn't need to be entered as an array formula.
The guidelines that I use when determining whether
or not to use an array formula are:
1. Do any of its functions have cell references
that are arrays (e.g., A1:A15), and the Help topic
for that function doesn't explictly indicate that
the function is designed to accept or "recognize"
arrays.
2. The results of the formula produces an array,
as the TREND() function does.
In fact, the Help topic for TREND() is a good
example of both cases!
Posted by Aladin Akyurek on March 19, 2001 8:10 AM
To Array or Not to Array
So I did it again. Anycase, not Umlas's fault. A cognitive failure as a result of my associating Umlas with arrays. Good guidelines, by the way.
Aladin
Posted by Dave Hawley on March 19, 2001 8:23 PM
Formula reduction
Aladin
I believe for the example given you could use:
=LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on March 19, 2001 9:58 PM
Re: Formula reduction
Right Dave. You don't need those SUMs around the terms. Hey, that must have another reason why I made it an array-formula after copying it from Umlas.
Aladin
Posted by Dave Hawley on March 19, 2001 10:40 PM
Re: Formula reduction
He he, It is unusual fo Bob to write a simple formula :o)
Posted by cj on March 20, 2001 4:33 AM
Re: Formula reduction
Result is case sensitive surprisingly (well it
surprised me anyway.
Posted by Dave Hawley on March 20, 2001 5:46 AM
Re: Formula reduction
Result is case sensitive surprisingly (well it
Well spotted! Maybe this:
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("x"),""))
Dave
OzGrid Business Applications
