I would like to count the number of times a value appears in a column of cells, where each cell can contain multiple numbers and number ranges. Comma (,) is used as the separator between numbers and dash (-) is used to indicate range. Also, numbers and ranges within a cell do not overlap. In this case, I am looking for a solution without separating out the numbers and range values across additional cells.

To simply the ask, identifying if the value appears within a single cell is my main roadblock primarily due to the multiple numbers ranges.

Sample screenshot (https://i.sstatic.net/v8eBVLZo.png)

I have been only successful to check if a value exists when the cell only contains a single number range using the formula: =ISBETWEEN(B1, value(REGEXEXTRACT(A8,"(.)-.")), value(REGEXEXTRACT(A8,".-(.)")))

In the screenshot example, I would like to count the number of lists that 7 appears in, the answer should be 3.

Again to simplify, requesting help for how to validate if a value appears within a single cell that has multiple numbers and ranges.

Splitting Values and processed it depending if a Symbol is with the Value

For starters you are actually on a Good start already, now what you want right now is splitting values depending on the delimiter you want to split it and process each value accordingly.

Sample Formula

This is a sample formula based on the attached markdown table.

=COUNTIF(BYROW(B2:B5, LAMBDA(r, OR(SCAN("",SPLIT(REGEXREPLACE(r,"-","@"),","), LAMBDA(a,c, IF(REGEXMATCH(TO_TEXT(c),"@"),ISBETWEEN(A2,CHOOSECOLS(SPLIT(c,"@"),1), CHOOSECOLS(SPLIT(c,"@"),2)), c =A2)))))), TRUE)

Markdown Table:

Though what you said is 3 I do think that this is an error and what you mean is only 2.

References:

Scan - Google Sheets

Split - Google Sheets