On Fri, 15 Dec 2006 18:25:00 -0800, Bob wrote:

Is there a way to count the total number of cells in a worksheet that contain
an apostrophe as the first character? I've tried using COUNTIF and
SUMPRODUCT, but to no avail. Any help would be greatly appreciated.
Thanks,
Bob

I may be wrong, but I think that if the "'" is the first character in a cell,
it is treated as a prefix indicating that the cell is TEXT, rather than as
"cell contents".

So if a cell contains '123, you would only see displayed 123.

If this is what you are trying to figure out, then you need to check the cell
prefix.

The CELL worksheet function does not return an array; so if you were going to
do that, you would require a helper column with the formula:

=CELL("prefix",cell_ref)

copy drag down.

Then use COUNTIF to count the number of apostrophes in the helper column.

Or you could use VBA.

Or you could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr


and use the formula:

=SUMPRODUCT((XLM.GET.CELL(52,rng)="'")*1)

where rng is the range of cells to check.


--ron