How to count cells containing numbers in specific range with cells that contain both text and numbers

Question

I thought I could easily sort this issue out but it took me ages to solve just half of it.

I have a table that contains 100 data cells in a row. Data in each cell are either text-only or text and numbers (see layout at bottom).

I need a function that COUNTs how many cells are present in the table that report the value of N2 OR E to be >=37.

Negative
Positive (N2: 23, E: 23)
Negative                      Function answer: 2
Positive (N2: 37, E: 26)
Positive (N2: 31, E: 38)

So far I could only extract each N2 number with a function [=MID(A2,15,FIND(",",A2)-15)] that considers the 15th character, then a second function counts how many extracted numbers (they have been extracted in B row) are >=37, [=COUNTIF(B2:B100, ">=37")] but have not a clue on how to take the E value into account.

In addition, I would like the function to consider cells containing the N2 value OR the E value >=37.

Is there the chance to have one big function that does that? Is there the chance not to rely on KUTools for Excel?

Answer 1

If you have the newest version of Excel, you can use FILTERXML after making some minor changes. First concatenate the whole range with CONCAT, then eliminate all ","s and replace ")"s with spaces in the concatenated string.

For example, the below gets you all the instances over 36 (if you only want the number of times, wrap it in a COUNT):

=FILTERXML("<t><s>"&SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(CONCAT($F$2:$F$7), ")", " "), ",", ""), " ",
 "</s><s>")&"</s></t>", "//s[number()>=37]")

For more info on dealing with strings, see here.

EDIT: Thanks @MarkBalhoff for catching a missing space in the formula and @JvdV for giving another way with =IFERROR(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(" ",,F2:F6)," ","</s><s>")&"</s></t>","//s[translate(.,',','')*1>=37 or translate(following::*[2],')','')*1>=37]")),0)

Answer 2

Since you include the python tag and also reference KU-Tools, I assume you have some familiarity with VBA.

You could easily, and flexibly, implement the logic in Excel VBA using regular expressions.

For this function, I allowed three arguments:

1. The range to search
2. The threshold for the values
3. A list of values to look for

In the regex, the pattern looks for the digits that follow either of the strings in "searchFor". Note that, as written, you need to include the colons in the searchFor strings, and that that the strings are case-sensitive. (easily changed)

Option Explicit
Function CountVals(r As Range, Threshold As Long, ParamArray searchFor() As Variant) As Long
    Dim RE As Object, MC As Object, M As Object
    Dim counter As Long
    Dim vSrc As Variant, v As Variant
    Dim sPat As String
    
'read range into variant array for fastest processing
vSrc = r

'create Pattern
sPat = "(?:" & Join(searchFor, "|") & ")\s*(\d+)"

'initialize regex
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = False 'or change to true if capitalization not important
    .Pattern = sPat
    counter = 0
    
    'check each string for the values
    For Each v In vSrc
        Set MC = .Execute(v)
            For Each M In MC
                If CLng(M.submatches(0)) >= Threshold Then counter = counter + 1
            Next M
    Next v
    
CountVals = counter
        
End With
    
End Function