# Thread: Countif formula to count number in a range

1. ## Countif formula to count number in a range

I have a column of numbers and need to count how many 1's, 2's, 3's occur. I've tried =countif(a1:a6,"1*") as an array formula but don't get the correct answer.

135
2345
12345
234
45
145

2. Try {=SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6,"1","")))}

3. WebGenii beat me to the punch...nice job. I keep forgetting about the power of array formulas! Anyway attached is a workbook with the UserDefined function I was working on {just another approach but not as efficient as the array formula} as well as a column using Catherine's solution modified so it is copyable down the rows for each succeeding digit. There is always more than one way to skin that proverbial CAT!

4. Or for a total
{=SUM((LEN(A1:A6)-LEN(SUBSTITUTE(A1:A6,TRANSPOSE(\$b\$1:\$b\$3),""))))}

5. array-enter:
=COUNT(FIND("1",A1:A6))

6. That will work, but it will only pick up the first instance of the character in the string. I guess it depends on the format of the data.

7. Dang, busted again by the WebGenii. I'm not going to rub your lamp any more...
Here is a fixed UDF.

8. True - I confess I made that assumption based on the sample data and the initial reference to COUNTIF.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•