Countif formula to count number in a range

Topic

New Reply

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

Reply | Quote

Replies

Try {=SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6,”1″,””)))}

Reply | Quote

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!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

array-enter:
=COUNT(FIND(“1”,A1:A6))

Reply | Quote

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.

Reply | Quote

Dang, busted again by the WebGenii. I’m not going to rub your lamp any more…:lol:
Here is a fixed UDF.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote