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

Try this:

=SUMPRODUCT(--(LEFT(A1:A10)="'"))

After the = is a double quote ", an apostrophe ' (or single quote) then
another double quote ".

Or:

=SUMPRODUCT(--(LEFT(A1:A10)=CHAR(39)))

Biff

"Bob" wrote in message
...
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 have already tried your two suggestions, but with no luck.
Thanks all the same.
Bob


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(LEFT(A1:A10)="'"))

After the = is a double quote ", an apostrophe ' (or single quote) then
another double quote ".

Or:

=SUMPRODUCT(--(LEFT(A1:A10)=CHAR(39)))

Biff

"Bob" wrote in message
...
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

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

Ron,
Thanks! I prefer to use a function that returns an array, so I will
download Longre's free morefunc.xll add-in and try it out. Thanks again for
your help.
Bob


"Ron Rosenfeld" wrote:

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

On Fri, 15 Dec 2006 20:33:01 -0800, Bob wrote:

Ron,
Thanks! I prefer to use a function that returns an array, so I will
download Longre's free morefunc.xll add-in and try it out. Thanks again for
your help.
Bob

You're welcome. Let me know if it does what you want.
--ron

Ron,
The morefunc.xll add-in works great! Using the XLM.GET.CELL function tells
me that I have 100+ instances of an apostrophe in my worksheet! But when I
try to go find them using Excel's Find command, I can't find any. As the
search criteria, I used "'" (double quotes, apostrophe, double quotes). Can
you advise me as to what I'm doing wrong?
Thanks again.
Bob


"Ron Rosenfeld" wrote:

On Fri, 15 Dec 2006 20:33:01 -0800, Bob wrote:

Ron,
Thanks! I prefer to use a function that returns an array, so I will
download Longre's free morefunc.xll add-in and try it out. Thanks again for
your help.
Bob

You're welcome. Let me know if it does what you want.
--ron

On Mon, 18 Dec 2006 03:13:00 -0800, Bob wrote:

Ron,
The morefunc.xll add-in works great! Using the XLM.GET.CELL function tells
me that I have 100+ instances of an apostrophe in my worksheet! But when I
try to go find them using Excel's Find command, I can't find any. As the
search criteria, I used "'" (double quotes, apostrophe, double quotes). Can
you advise me as to what I'm doing wrong?

The FIND command is designed to locate text strings. As I wrote previously,
the "'" is a prefix indicating that the cell is to be treated as TEXT. Not the
same thing as a text string within a cell.

I'm not sure what you're trying to do. If you want to locate these cells and
do something to them, you could use a VBA macro.

e.g.:

======================
Sub FindApos()
Dim c As Range
Dim RngToCheck As Range

Set RngToCheck = [a1:c20]

For Each c In RngToCheck
If c.PrefixCharacter = "'" Then
c.Interior.Color = vbRed
'or do something else here
End If
Next c
End Sub
======================
--ron

Ron,
Sorry for the confusion on my part. Thanks for the macro! I sincerely
appreciate it.
Bob


"Ron Rosenfeld" wrote:

On Mon, 18 Dec 2006 03:13:00 -0800, Bob wrote:

Ron,
The morefunc.xll add-in works great! Using the XLM.GET.CELL function tells
me that I have 100+ instances of an apostrophe in my worksheet! But when I
try to go find them using Excel's Find command, I can't find any. As the
search criteria, I used "'" (double quotes, apostrophe, double quotes). Can
you advise me as to what I'm doing wrong?

The FIND command is designed to locate text strings. As I wrote previously,
the "'" is a prefix indicating that the cell is to be treated as TEXT. Not the
same thing as a text string within a cell.

I'm not sure what you're trying to do. If you want to locate these cells and
do something to them, you could use a VBA macro.

e.g.:

======================
Sub FindApos()
Dim c As Range
Dim RngToCheck As Range

Set RngToCheck = [a1:c20]

For Each c In RngToCheck
If c.PrefixCharacter = "'" Then
c.Interior.Color = vbRed
'or do something else here
End If
Next c
End Sub
======================
--ron

On Mon, 18 Dec 2006 10:46:02 -0800, Bob wrote:

Ron,
Sorry for the confusion on my part. Thanks for the macro! I sincerely
appreciate it.
Bob

You're very welcome. Hopefully the macro will help accomplish what it is you
require.

Best wishes,
--ron