CODEWITHSUNDEEP
vbaexcelcountdigit
Georgia
Georgia

Reputation: 1

Count the number of instances of a digit in a range of cells

If I have a column and the cells are 

579
39
357
3467
34579

I want to be able to count the number of times any of the digits occurs. For example, 3 occurs 4 times in this range.

I tried using the countif function, but this seems to only work if the only thing in the cell is a 3.

Is this possible using a function, or will I have to do a workaround?

Upvotes: 0

Views: 1572

Answers (1)

user4039065
user4039065

Reputation:

Try using using Application Evaluate on a native SUMPRODUCT function.

application.evaluate("SUMPRODUCT(LEN(A2:A99)-LEN(SUBSTITUTE(A2:A99, 3, """")))")

This will count multiple occurrences within a single value; e.g. 233453 hold 3 occurrences of 3.

If I was doing this, I would qualify the parent worksheet in those cell range addresses to avoid any confusion. The external parameter of the Range.Address property can help you construct a string to be evaluated as a formula.

Upvotes: 3

Related Questions