Counting Odd and Even numbers in a comma-separated list

BrSuthe

New Member
Joined
Feb 10, 2018
Messages
29
Is there a formula(s) to count Odd and Even numbers in a comma-separated list? please. (The list may have numbers up to 20. Single or double digit positive numbers only).
Thank you.
 
Last edited:
Caveats: No spaces after the numbers and less than 100 characters in the cell (this can be raised by changing the 100 and 99 so long as they remain 1 different from each other).

These are array-entered** formulas...

Even: =SUM(IF(MID(A1&",",ROW(2:100),1)=",",1-MOD(MID(A1&",",ROW(1:99),1),2),0))

Odd: =SUM(IF(MID(A1&",",ROW(2:100),1)=",",MOD(MID(A1&",",ROW(1:99),1),2),0))

**Commit these formulas using CTRL+SHIFT+ENTER and not Enter by itself
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
And yet another possibility, creating your own function in VBA:
Code:
Function CountNums(rng As Range, typ As String) As Long

    Dim arr() As String
    Dim i As Long
    Dim ct As Long
    
    arr = Split(rng, ",")
    
    For i = LBound(arr) To UBound(arr)
        Select Case UCase(typ)
            Case "ODD"
                If Application.WorksheetFunction.IsOdd(arr(i)) Then ct = ct + 1
            Case "EVEN"
                If Application.WorksheetFunction.IsEven(arr(i)) Then ct = ct + 1
        End Select
    Next i
    
    CountNums = ct
    
End Function
So, then you would just use it like:
=CountNums(A1,"EVEN")
=CountNums(A1,"ODD")

While I would recommend using a formula for this (I kind of favor the one I posted in Message #11:LOL:), your UDF approach caught my eye, so I thought I would try my hand at it. Here is what I came up with...
Code:
Function CountNums(S As String, Typ As String) As Long
  Dim Pattern As String, V As Variant
  If Typ = "" Then Exit Function
  Pattern = Choose(1 + (4 + InStr("/EVEN/ODD/", UCase("/" & Typ & "/"))) \ 5, "|", "*[02468]", "*[13579]")
  For Each V In Split(Replace(S, " ", ""), ",")
    CountNums = CountNums - (V Like Pattern)
  Next
End Function
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top