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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Raw[/td][td][/td][td=bgcolor:#70AD47]IF[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]2,3,1,15,44,51,32,7,9,11[/td][td][/td][td=bgcolor:#E2EFDA]Even[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Odd[/td][td]

7​

[/td][/tr]
[/table]

[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
Raw	IF	Count
2,3,1,15,44,51,32,7,9,11	Even
Odd

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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.