Counting single occurrences of a number in a range

Question

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

preagan · Answer

Hello chrisrcox,&nbsp;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 + EnterSee attached file.&nbsp;

patrick2788 · Answer

chrisrcox&nbsp;&nbsp;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))&nbsp;Workbook attached.

patrick2788 · Answer

PReagan&nbsp;Good work. 365 will calculate it without the CSE.

preagan · Answer

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

twifoo · Answer

chrisrcox&nbsp;Given your specified parameters, this non-array formula returns your desired results:&nbsp;I prefer SUMPRODUCT instead of SUM, which also returns the same results if confirmed with Ctrl+Shift+Enter.

Forum Discussion

Counting single occurrences of a number in a range

5 Replies

Resources