| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Count | New Users to Excel | |||
| Count on cell for each time it is changed | Excel Discussion (Misc queries) | |||
| I need to keep a count of the times a cell is changed | Excel Discussion (Misc queries) | |||
| cell color index comparison | New Users to Excel | |||
| Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Discussion (Misc queries) | |||
Linear Mode
