Feb 24 2020 10:51 AM
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
Feb 25 2020 07:46 AM
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.
Feb 25 2020 09:32 AM - edited Feb 25 2020 09:32 AM
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.
Feb 25 2020 09:51 AM
Good work. 365 will calculate it without the CSE.
Feb 25 2020 10:06 AM
Feb 26 2020 12:32 AM - edited Feb 26 2020 12:41 AM
Given your specified parameters, this non-array formula returns your desired results:
I prefer SUMPRODUCT instead of SUM, which also returns the same results if confirmed with Ctrl+Shift+Enter.