Count values in cells that fall between a range, with dash delimiter separator

[sans]

Hello,

I am looking for a way to count how many values fall between a range, where the values in a cell(s) are separated by a dash. I have been looking everywhere but haven't been able to find a solution.

I have to process very large amounts of data so a function to carry out this task would save me a lot of time though I would be grateful with any solution.

Thank you

[Richard Buttrey]

Hi,

Maybe someone will come up with a more complex array formula, but if you want a pragmatic solution with helper columns, try the attached.
I first copied C6 to G6 and then used Text to Columns with the "-" as a delimiter, and then did the same with D6 to L6

Then used the formula in E6

[dilipandey]

HI Sans,

Use below formula which need to be entered with key combination: ctrl shift enter :-

Please Login or Register  to view this content.

Count values separated by delimiter.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[sans]

Hi Richard and dilipandey,

Thank you both very much for the replies! I tried both solutions and they both work equally well. One little problem, I will be mainly analyzing large and varying amounts of data where cells contain values separated by a delimiter. I am attaching another example where you can see what I mean. The actual data span down to 3000 rows (in many workbooks). Is it possible in this case to convert your solutions into a udf solution to achieve the same result?

Thanks again!

[dilipandey]

Okay.. so here you would be comparing row 6 results and row 7 results to check if those two are falling in between E1:E2 .. correct?

If yes, what should be the answer (just for cross checking the formula results) ? Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[nilem]

maybe so

Please Login or Register  to view this content.

[jindon]

UDF

In cell
=CountBetween(C6:L11,"-",">="&E1,"<="&E2)

Please Login or Register  to view this content.

[sans]

Hi guys,

Thank you very much for the replies, that's exactly the results I was looking for to achieve! Both udf's work fantastic!

nilem, out of curiosity, how does the function still give the correct result for row 12 in Example sheet 2, where the values are separated with a * ?

[nilem]

dash "-" is the default delimiter, but if you use a different delimiter (for example, the tilde "~"), you can specify it explicitly in the formula, such as this

Please Login or Register  to view this content.

[sans]

Hi nilem,

That's super convenient. Having this option there's certainly no need for different udf variations.

Thank you very much for the great udf. Much appreciated!

[sans]

Hello,

I was wondering if it would be possible to request an additional minor version based on any of the above solutions.

Is it possible to receive as a result the max count found from any one of the cells in a range?

Thank you very much for all the help.

[jindon]

Please Login or Register  to view this content.

[sans]

Hi jindon,

That was fast! Thank you very much, I didn't expect to receive such a quick response.

Works absolutely great. Again thank you for the help!

[sans]

Hello jindon,

Your last macro works great, just have a question regarding the time it takes to calculate. I am currently calculating 130 cells in each row, (i.e. A4:DZ4) and going down around 2000 rows. It takes a little over than 20 minutes to calculate the one sheet (I'll be running the same calculation on 130 sheets every few days). Simply wanted to double check if the time it takes to finish the calculation is to be expected.

Thank you for the help.

[jindon]

Not tested but try change to
=CountBetween(A4:DZ4,"-",$E$1,$E$2)

Please Login or Register  to view this content.

See if any difference.

[sans]

Hi jindon,

Thank you for the reply! The calculation dropped quite dramatically, approximately 3 minutes to calculate the whole sheet. Thank you for that.

I have in total 130 workbooks and each workbook has the one sheet. Is it possible to minimize the calculation time even more?

Thank you for getting back to me.

[sans]

Hello,

Sorry to be resurrecting the thread again, I encountered today another small hurdle and was wondering if it would be possible to request one last variation based on the original macros you've helped me with, in post #6 and #7. Is it possible to receive the result of only the odd values that fall between a range? I don't know if this requires a new thread, if it does please let me know.

Thank you for all the help, really appreciated.

[deevan]

guys help required i want to know that how to calculate the count of different values i.e count the values in between 200 to 300.

regards.

[sans]

deevan,

You can use

Please Login or Register  to view this content.

[deevan]

Help required as sheet is attached.

Number of count of total customer item (column A)
number of count of transaction who purchased in between 200 to 300, 301 to 400, and so on different amount.
Number of count of transaction where we sold item below cost as profit is already in`column E.
Number of count of profit percentage i. e 60 %, 30% etc.




Dear San,
Thanks for you response.
I want to count the below mentioned data in condition that total number of count where customer purchased more than 200, 300 400, 500, 600(different range) and also compare profit whether it is below cost or not. . I will appreciate if you can help as my data sheet is more than 80k records.

Sale Amount Cost Profit GM
202.6 145.586 57.014 3.553513172
354.57 220.3262 134.2438 2.641239297
318.84 241.4118 77.4282 4.117879532
238.65 166.3699 72.2801 3.301738653

[deevan]

Dear Friends,

urgent help required.