CODEWITHSUNDEEP
excelexcel-formula
Tiffany
Tiffany

Reputation: 11

Need to count the times a digit occurs in an excel column

Here is the situation- I have a column of equipment numbers, and the warehouse needs to order stickers to place on equipment in the field. They order the stickers per letter or numeral, not a string of numbers (Like T, 1, 2, 3, rather than T123).

How can I count the number or times a "1" appears in my column in excel? So far I have used countif(range, "asterisk1asterisk"), which returns the count of cells that contain a one, but many cells have multiple ones which are not included in count. This will be so handy if I can figure it out, and I appreciate your help! Thanks, -Tiffany

Upvotes: 1

Views: 1272

Answers (1)

barry houdini
barry houdini

Reputation: 46331

If you have data in A2:A100 then use this formula to count the number of 1s in that range

=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,"1","")))

That works because you compare the total lengths of the cells against the total lengths with the 1s replaced by nothing - that gives you the total number of 1s - you can do the same with "T" (but note, it's case-sensitive, so with "T" it counts only "T"s not "t"s)

Upvotes: 1

Related Questions