Count occurances of numbers in a particular range in a single cell

**dekoay** · 05-10-2012, 08:38 PM

Hi, I have this problem I can't solve.

I have some data I need to sort out. It's presented this format:

045 385 195 375 193 586 395 183 659 395 (all in a single cell. There's actually over a hundred 3-digit numbers but I just pasted 10 here)

I need to count the occurrences of 3-digit numbers under 300. Please help!!

(btw I can't do it manually, as I have hundreds of such cells to count!)

thanks!

**JBeaucaire** · 05-10-2012, 09:09 PM

This is easier to test if you posted a workbook with some actual data and sample "results". If understand this:

1) A cell (A1) has a string of 300 3-digit codes separate by spaces (no returns, line feeds, just spaces)
2) You want a count (a single number) of all the number lower than 300.

So, in the example:

045 385 195 375 193 586 395 183 659 395

... the answer would simply be: 4

Here's a UDF you can put in a standard code module (Insert > Module)

Please Login or Register  to view this content.

That is then used in another cell to evaluate one or more cells. The function is used like so:

=ARRCOUNT(RangeOfCells, Threshold, Optional Delimiter)

For example:
=ARRCOUNT(A1, 300, " ")

...or this, which omits the delimiter causing the function to default to spaces:
=ARRCOUNT(A2:A12, 100)

http://screencast.com/t/UOx6Agg2QD

**StevenM** · 05-10-2012, 09:20 PM

The function Count3DigitNumbers counts each three digit number in a string separated by spaces.
It returns a string with the occurrence of each number.
Thus: "045 385 195 045 375 193 193 586 395 183 659 395"
Becomes: "045:2;183:1;193:2;195:1"
045 occurred 2x; 183 once; 193 2x; 195 once.
Run: Run_Count3DigitNumbers for an example.

Please Login or Register  to view this content.

**dekoay** · 05-10-2012, 09:29 PM

Thanks for your replies! Sorry this is a noob question, but where do I put in standard code modules?

**StevenM** · 05-10-2012, 09:36 PM

(1) One needs a Microsoft Office Excel Macro-Enabled Worksheet (.xlsm). You can create one by saving an excel file "Save as" Macro-Enabled Worksheet. (Your Example Worksheet appeared to be an Excel Macro-Enabled Worksheet.)

(2) Your Macro-Enabled Worksheet needs to be in a folder which you can designate was macro friendly. The way you do this: click on the Office Button -> Excel Options -> Trust Center -> Trust Center Setting -> Trusted Locations. Then you add your folder (where you have your Excel Macro-Enabled Worksheet) as a trusted location.

(3) With your Excel Macro-Enabled Worksheet go to the menu item "Developer" -> Visual Basic.

(4) In the VBE (Visual Basic Editor), go to Tools -> Options and click the "Editor" tab and check the box next to "Require Variable Declarations" (if not checked). (This is not necessary, but good practice. Only needs to be done once.)

(5) From the menu: Insert -> Module; a module should open to the right with "Option Explicit" (in blue type).

(6) Copy and paste any code under "Option Explicit":

If you get stuck on a step, just ask.