Count # of "alt+0242" in cell
 

I need a formula that will count the number of occurences the above is found
in a row. Sometimes this will appear more than once in a cell. (They are
wingdings checkmark or the character 'u' with two dots above - just provided
the ascii code above (all are the same)).

Thanks!

Annette

Count # of "alt+0242" in cell
 

=SUMPRODUCT(N(CHAR(242)=MID(A1,ROW(1:255),1)))
Bob Umlas
Excel MVP

"Annette" wrote in message
...
I need a formula that will count the number of occurences the above is
found
in a row. Sometimes this will appear more than once in a cell. (They are
wingdings checkmark or the character 'u' with two dots above - just
provided
the ascii code above (all are the same)).

Thanks!

Annette

Count # of "alt+0242" in cell
 

Very interesting. FWIW, I made the following modification as it looked like
the OP wanted the entire row. I tried the original formula both normally
entered and array entered and it only seemed to use A1. The modified formula
does not need to be array entered.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(1:255),1)))

Seemed to work for me. Hmm, that's also interesting. I put A1:IV1 and I
just noticed that Excel changed it to 1:1. BTW, I recently read an article
you wrote on array formulas (a link to which I found on Dick's blog.) Very
informative article. Thanks.

--
Kevin Vaughn


"Bob Umlas" wrote:


=SUMPRODUCT(N(CHAR(242)=MID(A1,ROW(1:255),1)))
Bob Umlas
Excel MVP

"Annette" wrote in message
...
I need a formula that will count the number of occurences the above is
found
in a row. Sometimes this will appear more than once in a cell. (They are
wingdings checkmark or the character 'u' with two dots above - just
provided
the ascii code above (all are the same)).

Thanks!

Annette

Count # of "alt+0242" in cell
 

This one worked ... THANKS!

(yes .. I was yelling!)

"Kevin Vaughn" wrote in message
...
Very interesting. FWIW, I made the following modification as it looked
like
the OP wanted the entire row. I tried the original formula both normally
entered and array entered and it only seemed to use A1. The modified
formula
does not need to be array entered.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(1:255),1)))

Seemed to work for me. Hmm, that's also interesting. I put A1:IV1 and I
just noticed that Excel changed it to 1:1. BTW, I recently read an
article
you wrote on array formulas (a link to which I found on Dick's blog.)
Very
informative article. Thanks.

--
Kevin Vaughn


"Bob Umlas" wrote:


=SUMPRODUCT(N(CHAR(242)=MID(A1,ROW(1:255),1)))
Bob Umlas
Excel MVP

"Annette" wrote in message
...
I need a formula that will count the number of occurences the above is
found
in a row. Sometimes this will appear more than once in a cell. (They
are
wingdings checkmark or the character 'u' with two dots above - just
provided
the ascii code above (all are the same)).

Thanks!

Annette

Count # of "alt+0242" in cell
 

I would amend the formula to use the INDIRECT formula to prevent
the row references from changing if you insert rows.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(INDIRECT("1:25 5")),1)))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Kevin Vaughn" wrote in
message
...
Very interesting. FWIW, I made the following modification as
it looked like
the OP wanted the entire row. I tried the original formula
both normally
entered and array entered and it only seemed to use A1. The
modified formula
does not need to be array entered.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(1:255),1)))

Seemed to work for me. Hmm, that's also interesting. I put
A1:IV1 and I
just noticed that Excel changed it to 1:1. BTW, I recently
read an article
you wrote on array formulas (a link to which I found on Dick's
blog.) Very
informative article. Thanks.

--
Kevin Vaughn


"Bob Umlas" wrote:


=SUMPRODUCT(N(CHAR(242)=MID(A1,ROW(1:255),1)))
Bob Umlas
Excel MVP

"Annette" wrote in message
...
I need a formula that will count the number of occurences
the above is
found
in a row. Sometimes this will appear more than once in a
cell. (They are
wingdings checkmark or the character 'u' with two dots
above - just
provided
the ascii code above (all are the same)).

Thanks!

Annette

Count # of "alt+0242" in cell
 

Good point.
--
Kevin Vaughn


"Chip Pearson" wrote:

I would amend the formula to use the INDIRECT formula to prevent
the row references from changing if you insert rows.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(INDIRECT("1:25 5")),1)))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Kevin Vaughn" wrote in
message
...
Very interesting. FWIW, I made the following modification as
it looked like
the OP wanted the entire row. I tried the original formula
both normally
entered and array entered and it only seemed to use A1. The
modified formula
does not need to be array entered.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(1:255),1)))

Seemed to work for me. Hmm, that's also interesting. I put
A1:IV1 and I
just noticed that Excel changed it to 1:1. BTW, I recently
read an article
you wrote on array formulas (a link to which I found on Dick's
blog.) Very
informative article. Thanks.

--
Kevin Vaughn


"Bob Umlas" wrote:


=SUMPRODUCT(N(CHAR(242)=MID(A1,ROW(1:255),1)))
Bob Umlas
Excel MVP

"Annette" wrote in message
...
I need a formula that will count the number of occurences
the above is
found
in a row. Sometimes this will appear more than once in a
cell. (They are
wingdings checkmark or the character 'u' with two dots
above - just
provided
the ascii code above (all are the same)).

Thanks!

Annette

Count # of "alt+0242" in cell
 

Chip,

Could you avoid making the formula volatile by using COLUMN() instead of
ROW() & INDIRECT?

=SUMPRODUCT(N(CHAR(242)=MID(1:1,COLUMN(1:255),1)))

According to Charles Williams, although COLUMNS() is documented by Microsoft
as being volatile, in tests it does not seem to be so I assume that COLUMN()
also is not volatile.

--
Regards

Sandy

with @tiscali.co.uk


"Chip Pearson" wrote in message
...
I would amend the formula to use the INDIRECT formula to prevent the row
references from changing if you insert rows.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(INDIRECT("1:25 5")),1)))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Kevin Vaughn" wrote in message
...
Very interesting. FWIW, I made the following modification as it looked
like
the OP wanted the entire row. I tried the original formula both normally
entered and array entered and it only seemed to use A1. The modified
formula
does not need to be array entered.

=SUMPRODUCT(N(CHAR(242)=MID(1:1,ROW(1:255),1)))

Seemed to work for me. Hmm, that's also interesting. I put A1:IV1 and I
just noticed that Excel changed it to 1:1. BTW, I recently read an
article
you wrote on array formulas (a link to which I found on Dick's blog.)
Very
informative article. Thanks.

--
Kevin Vaughn


"Bob Umlas" wrote:


=SUMPRODUCT(N(CHAR(242)=MID(A1,ROW(1:255),1)))
Bob Umlas
Excel MVP

"Annette" wrote in message
...
I need a formula that will count the number of occurences the above is
found
in a row. Sometimes this will appear more than once in a cell. (They
are
wingdings checkmark or the character 'u' with two dots above - just
provided
the ascii code above (all are the same)).

Thanks!

Annette