Count # of "alt+0242" in cell

A

Annette

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
 
K

Kevin Vaughn

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.
 
C

Chip Pearson

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:255")),1)))


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


message
 
K

Kevin Vaughn

Good point.
--
Kevin Vaughn


Chip Pearson said:
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:255")),1)))


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


message
Click to expand...
 
S

Sandy Mann

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
[email protected]
[email protected] with @tiscali.co.uk
 
Top