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?

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

=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?
>
>