Counting single occurrences of a number in a range

Copper Contributor

This seemed so easy when I first looked this up, using sum len substitute etc. But as try as I might is imply cannot get it to work. I am using 365 for MAC which just maybe the issue, but the request is I thing fairly straight forward. I have a range of numbers spread over some fifty cell. each cell contains two digits representing a distance on an archery course. I have to make some markers for each distance and will use self adhesive numbers. I simply wanted to work out how many of each individual number do I need to order. i.e 23,28,55,57,78,87, etc I want to know how many 2's, 8's, 5's, 7's, 3's i need.

 

TIA Chris

5 Replies

@chrisrcox 

 

Try this:

=SUM(IF(MID($A$1:$A$25,{1,2},1)=TEXT(D2,"0"),1,0))+SUM(IF(MID($B$1:$B$25,{1,2},1)=TEXT(D2,"0"),1,0))

 

Workbook attached.

Hello @chrisrcox,

 

As a variant:

=SUM(LEN($A$2:$C$11)-LEN(SUBSTITUTE($A$2:$C$11,$E2,"")))/LEN($E2)

This is an array formula so press Ctrl + Shift + Enter

See attached file. 

@PReagan 

Good work. 365 will calculate it without the CSE.

@Patrick2788 

 

Thank you @Patrick2788. I have not upgraded yet so that is good to know!

@chrisrcox 

Given your specified parameters, this non-array formula returns your desired results: 

Character Count.PNG

I prefer SUMPRODUCT instead of SUM, which also returns the same results if confirmed with Ctrl+Shift+Enter.