Counting Odd and Even numbers in a comma-separated list

[BrSuthe]

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.

 

[sandy666]

with PowerQuery

Raw		IF	Count
2,3,1,15,44,51,32,7,9,11		Even	3​
		Odd	7​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Raw", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),
    Type = Table.TransformColumnTypes(Split,{{"Raw", Int64.Type}}),
    Condition = Table.AddColumn(Type, "IF", each if Number.IsEven([Raw]) then "Even" else if Number.IsOdd([Raw]) then "Odd" else null),
    #"Grouped Rows" = Table.Group(Condition, {"IF"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]

list can be much longer than 20

 

[kweaver]

One way would be to do a Text To Columns then apply these to the column range:

ODDS: =SUMPRODUCT((MOD(range,2)<>0)+0)

EVENS: =SUMPRODUCT((MOD(range,2)=0)+0)

 

[Eric W]

It can also be done with formulas:

Excel 2012

	A	B	C	D
1	Raw		IF	Count
2	2,3,1,15,44,51,32,7,9,11		Even	3
3			Odd	7

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula D2 {=SUM(IFERROR(MOD(MID(SUBSTITUTE(A2,",",REPT(" ",200)),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}*200+1,200)+1,2),0))} D3 {=SUM(IFERROR(MOD(MID(SUBSTITUTE(A2,",",REPT(" ",200)),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}*200+1,200)+0,2),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Slightly shorter version of D2:

=SUM(IFERROR(MOD(MID(SUBSTITUTE(A2,",",REPT(" ",200)),(ROW(INDIRECT("1:19"))-1)*200+1,200)+1,2),0))
with CSE.

 

[Joe4]

And yet another possibility, creating your own function in VBA:

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")

 

[Tetra201]

Yet another approach (caveat: no spaces after the numbers):

even =(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&",","0,",""),"2,",""),"4,",""),"6,",""),"8,",""))+1)/2
odd=(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&",","1,",""),"3,",""),"5,",""),"7,",""),"9,",""))+1)/2

 

[kweaver]

And, did you know that "never odd or even" is a palindrome?

 

[Joe4]

And, did you know that "never odd or even" is a palindrome?

I did not, that just made my day!
(what can I say, I am a math and word geek!)

 

[kweaver]

Joe, me too...I always found it curious that "Evian" was ... well, you write it in reverse. Remember, it's ONLY water!! LOL

 

[Marcelo Branco]

And, did you know that "never odd or even" is a palindrome?

Nice!

M.