Count If with Non-Consecutive Range

[cart0250]

Hello,

This counts the number of instances of X* within range M10:M16 and returns the value into cell M8.

Instead of M10:M16, can a non-consecutive range be used here? Such as (M10:M11, M13:M14, M16:M17).

Range("M8").Value = Application.WorksheetFunction.CountIf(Range("M10:M16"), "X*")

 

[DanteAmor]

Could be:

Range("M8").Value = Application.WorksheetFunction.CountIf(Range("M10:M11"), "X*") + _
                    Application.WorksheetFunction.CountIf(Range("M13:M14"), "X*") + _
                    Application.WorksheetFunction.CountIf(Range("M16:M17"), "X*")

Or:

  Dim nCount As Long
  Dim itm As Variant
 
  For Each itm In Array("M10:M11", "M13:M14", "M16:M17")
    nCount = nCount + Application.WorksheetFunction.CountIf(Range(itm), "X*")
  Next
  Range("M8").Value = nCount

Or:

  Dim c As Range
  Dim nCount As Long
  
  For Each c In Range("M10:M11, M13:M14, M16:M17")
    If UCase(c.Value) Like "X*" Then
      nCount = nCount + 1
    End If
  Next
  Range("M8").Value = nCount

 

[cart0250]

Could be:

Range("M8").Value = Application.WorksheetFunction.CountIf(Range("M10:M11"), "X*") + _
                    Application.WorksheetFunction.CountIf(Range("M13:M14"), "X*") + _
                    Application.WorksheetFunction.CountIf(Range("M16:M17"), "X*")

Or:

  Dim nCount As Long
  Dim itm As Variant
 
  For Each itm In Array("M10:M11", "M13:M14", "M16:M17")
    nCount = nCount + Application.WorksheetFunction.CountIf(Range(itm), "X*")
  Next
  Range("M8").Value = nCount

Or:

  Dim c As Range
  Dim nCount As Long
 
  For Each c In Range("M10:M11, M13:M14, M16:M17")
    If UCase(c.Value) Like "X*" Then
      nCount = nCount + 1
    End If
  Next
  Range("M8").Value = nCount

 

[cart0250]

thank you

 

[djclements]

Non-contiguous range references consist of multiple Areas, so you could also just loop thru the areas. For example, a custom function could be defined something like this:

Public Function CountIfByArea(reference As Range, criteria As Variant) As Long
    Dim i As Long, j As Long
    For i = 1 To reference.Areas.Count
        j = j + Application.WorksheetFunction.CountIf(reference.Areas(i), criteria)
    Next i
    CountIfByArea = j
End Function

Then, you can use the custom function directly in the worksheet:

=CountIfByArea((M10:M11,M13:M14,M16:M17),"X*")

Or, called in VBA:

Sub Test()
    Range("M8").Value = CountIfByArea(Range("M10:M11,M13:M14,M16:M17"), "X*")
End Sub

Incidentally, for those using a modern version of Excel (Office 365 or 2024), a custom LAMBDA function could also be defined in Name Manager for this task:

=LAMBDA(reference,criteria,REDUCE(0,SEQUENCE(AREAS(reference)),LAMBDA(a,v,a+COUNTIF(INDEX(reference,,,v),criteria))))

Cheers!

 

[Scott Huish]

With a modern version, you could also do this:

=COUNTA(LET(a,TOCOL((M10:M11,M13:M14,M16:M17)),FILTER(a,LEFT(a)="X")))

 

[Scott Huish]

But you could also do this:

=SUMPRODUCT(--(MOD(ROW(M10:M17)-ROW(M10),3)<>2),--(LEFT(M10:M17)="X"))

 

[djclements]

With a modern version, you could also do this:

=COUNTA(LET(a,TOCOL((M10:M11,M13:M14,M16:M17)),FILTER(a,LEFT(a)="X")))

Hi Scott,

Regarding this method, COUNTA is not really recommended as a suitable companion for the FILTER function (or any other array functions for that matter), as it is incapable of returning 0. If no matching records are found, the FILTER function returns #CALC! because "Empty arrays are not supported". COUNTA "Counts the number of cells in a range that are not empty", so anything other than a blank cell will be counted as 1, including errors and empty strings ("").

IFERROR(ROWS(...),0) would be an acceptable alternative because ROWS will also return #CALC! when FILTER does not find a match (assuming the [if_empty] argument is omitted). However, for this particular scenario, it might be more appropriate to simply SUM the results of the text comparison, using either a double-negative or the N function to convert Boolean values to 1's and 0's:

=SUM(--(LEFT(TOCOL((M10:M11,M13:M14,M16:M17)))="X"))

The COUNTIF lambda function I shared previously was merely a demonstration of how the exact same custom function could be created using LAMBDA instead of VBA. The biggest benefit of a COUNTIF based approach is that it offers basic wildcard functionality (*, ?, ~). The biggest drawback, however, is that it only works with a physical range reference.

Kind regards.

 

[Scott Huish]

I do like that SUM approach.

Yes, I was just trying to come up with a formula based approach too. I had used COUNTA before, but didn't realize the 0 result problem. Thanks