Count only Odd numbers between 1-15 from table of data

[Nascent23]

Hello!

I'm relatively to Excel and working on a lotto number generator for some fun, while also learning some more about Excel and formulas etc.

I've been able to put most of it together from searching and try and testing, but this one aspect, which seems like it should be easy, has been eluding me for days!

All I need to do quite simply is find the correct formula to calculate all odd numbers that appear in the table of lotto numbers for each week from 1-15, 16-30 and 31-45. This formula will show the summary for the entire table.

so there will be 3 separate formulas obviously, one for each section. I've tried combining the Sumproduct and MOD formula (counting odd numbers) with my countifs (between ranges 1-15 inclusive) but either receive an error or incorrect values.

Not sure if perhaps I need to use a VBA function for this particular task?

appreciate any advice or tips!

Thanks
Craig

[Sam Capricci]

maybe try this... =SUM(COUNTIF(Wednesday_Lotto_Results[['#1]:[S2]],{1,3,5,7,9,11,13,15}))
appears to work though I'm not going to sit down and count each, I'm sure you understand.

EDIT: oh, and mine, just adjust the range for each set, this for 16-30
=SUM(COUNTIF(Wednesday_Lotto_Results[['#1]:[S2]],{17,19,21,23,25,27,29}))
and for 31-45
=SUM(COUNTIF(Wednesday_Lotto_Results[['#1]:[S2]],{31,33,35,37,39,41,43,45}))

[XLent]

another option might be:

Please Login or Register  to view this content.

[Nascent23]

That's great - I knew it would be simple! I was playing around with using the actual number ranges {1,3} etc. but not doing it correctly, appreciate your help!

[Sam Capricci]

I'm not sure I'm giving you a count, I believe mine is giving you a sum of them because my totals do not match what xlent's total.
I finally applied a filter to the columns and did hand counts and my total was closer to xlent's than it was to mine. My sum though was on.
You might want to check.
And thank you for the rep though I'm not sure it was deserved.

[Nascent23]

No it looks all fine to me - just a note, is that rest of the formulas are only counting numbers 1-7 in case of the Oz Lotto one, and not the Supp numbers. And as the sets (1-15) etc. have a different amount of numbers, i.e. 8 odds, 7 evens I've just applied an addition to it to multiply formula by 86.666% or 113.3333% respectively to even out the variance in the sets due to one side having more numbers. But seems to work well! Thank you again!