Countif using Wild Card Characters
 

Objective: Trying to count the total number of times a character appears
in all cells within an array,

Assume the following data:
Cell A1 = 123
Cell A2 = 234
Cell A3 = 345
Cell A4 = 432
Using the formula =countif(A1:A4,”*3*”), I would assume this would give
me the count of the number of times the digit 3 appears within the
array, therefore in the example shown above, I would expect to see a 4
returned since the number 3 appears 4 times.. Instead, I get a count of
Zero (0).

On another worksheet, using this formula (on different data than shown
above) gave the correct answer until I changed the data in the array.
Deleting a number in one of the cells in the array, correctly resulted
in a corresponding decrease by 1 in the count result. However, adding
a number in a cell in the array would not result in an increase of one,
but instead, the result would stay the same as before I added the
number.
Am I overlooking something or is this formula with the asterisk not
suppose to work in the manner I am using it???? I am using Excel
2000.


A separate Forum operational question: If I get a response to
my submitted Thread and then I want to provide additional data to the
responder, how do I go about this?


--
nebb
------------------------------------------------------------------------
nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=511217

Countif using Wild Card Characters
 

I think you will need to format the cells as text to use that formula. If
you don't want to change the format, you can use this formula instead...

=SUMPRODUCT(--ISNUMBER(FIND("3",A1:A4)))

"nebb" wrote:


Objective: Trying to count the total number of times a character appears
in all cells within an array,

Assume the following data:
Cell A1 = 123
Cell A2 = 234
Cell A3 = 345
Cell A4 = 432
Using the formula =countif(A1:A4,€�*3*€�), I would assume this would give
me the count of the number of times the digit 3 appears within the
array, therefore in the example shown above, I would expect to see a 4
returned since the number 3 appears 4 times.. Instead, I get a count of
Zero (0).

On another worksheet, using this formula (on different data than shown
above) gave the correct answer until I changed the data in the array.
Deleting a number in one of the cells in the array, correctly resulted
in a corresponding decrease by 1 in the count result. However, adding
a number in a cell in the array would not result in an increase of one,
but instead, the result would stay the same as before I added the
number.
Am I overlooking something or is this formula with the asterisk not
suppose to work in the manner I am using it???? I am using Excel
2000.


A separate Forum operational question: If I get a response to
my submitted Thread and then I want to provide additional data to the
responder, how do I go about this?


--
nebb
------------------------------------------------------------------------
nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=511217

Countif using Wild Card Characters
 

=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,3,"")))

nebb wrote:
Objective: Trying to count the total number of times a character appears
in all cells within an array,

Assume the following data:
Cell A1 = 123
Cell A2 = 234
Cell A3 = 345
Cell A4 = 432
Using the formula =countif(A1:A4,”*3*”), I would assume this would give
me the count of the number of times the digit 3 appears within the
array, therefore in the example shown above, I would expect to see a 4
returned since the number 3 appears 4 times.. Instead, I get a count of
Zero (0).

On another worksheet, using this formula (on different data than shown
above) gave the correct answer until I changed the data in the array.
Deleting a number in one of the cells in the array, correctly resulted
in a corresponding decrease by 1 in the count result. However, adding
a number in a cell in the array would not result in an increase of one,
but instead, the result would stay the same as before I added the
number.
Am I overlooking something or is this formula with the asterisk not
suppose to work in the manner I am using it???? I am using Excel
2000.


A separate Forum operational question: If I get a response to
my submitted Thread and then I want to provide additional data to the
responder, how do I go about this?