Odd Number Count

[William53]

I have a problem that seems impossible to fix.
I want to count the odd numbers in a range above a certain number. E.g, count the number of odd numbers in the range above 50
I have tried SUMPRODUCT with ISODD and tried COUNTIFS but none seem work.

Any help would be very much appreciated.

 

[DRSteele]

Ya, ISODD makes an error when it's included as an array or range.

Here is an example. Adding the + character to the range inside ISODD gets it done. But it won't work in any of the IFS functions, so just turn the TRUEs and FALSEs into 1s and 0s and add it up.

After that, you can just add conditions inside the SUM function by multiplying together arrays that make TRUEs and FALSEs.

MrExcel posts19.xlsx
	C	D	E	F	G	H	I	J
4
5	9	count of ODDS				8	count of ODDS over	20
6
7	Number	per row	as range	as array
8	66	FALSE	#VALUE!	FALSE
9	96	FALSE		FALSE
10	66	FALSE		FALSE
11	20	FALSE		FALSE
12	75	TRUE		TRUE
13	45	TRUE		TRUE
14	13	TRUE		TRUE
15	40	FALSE		FALSE
16	27	TRUE		TRUE
17	4	FALSE		FALSE
18	59	TRUE		TRUE
19	63	TRUE		TRUE
20	32	FALSE		FALSE
21	66	FALSE		FALSE
22	85	TRUE		TRUE
23	14	FALSE		FALSE
24	81	TRUE		TRUE
25	54	FALSE		FALSE
26	24	FALSE		FALSE
27	23	TRUE		TRUE
Sheet30

Cell Formulas
Range	Formula
C5	C5	=SUM(--ISODD(+C8:C27))
H5	H5	=SUM(ISODD(+C8:C27)*(C8:C27>J5))
E8	E8	=ISODD(C8:C27)
F8:F27	F8	=ISODD(+C8:C27)
D8:D27	D8	=ISODD(C8)
Dynamic array formulas.

 

[William53]

Ya, ISODD makes an error when it's included as an array or range.

Here is an example. Adding the + character to the range inside ISODD gets it done. But it won't work in any of the IFS functions, so just turn the TRUEs and FALSEs into 1s and 0s and add it up.

After that, you can just add conditions inside the SUM function by multiplying together arrays that make TRUEs and FALSEs.

MrExcel posts19.xlsx
	C	D	E	F	G	H	I	J
4
5	9	count of ODDS				8	count of ODDS over	20
6
7	Number	per row	as range	as array
8	66	FALSE	#VALUE!	FALSE
9	96	FALSE		FALSE
10	66	FALSE		FALSE
11	20	FALSE		FALSE
12	75	TRUE		TRUE
13	45	TRUE		TRUE
14	13	TRUE		TRUE
15	40	FALSE		FALSE
16	27	TRUE		TRUE
17	4	FALSE		FALSE
18	59	TRUE		TRUE
19	63	TRUE		TRUE
20	32	FALSE		FALSE
21	66	FALSE		FALSE
22	85	TRUE		TRUE
23	14	FALSE		FALSE
24	81	TRUE		TRUE
25	54	FALSE		FALSE
26	24	FALSE		FALSE
27	23	TRUE		TRUE
Sheet30

Cell Formulas
Range	Formula
C5	C5	=SUM(--ISODD(+C8:C27))
H5	H5	=SUM(ISODD(+C8:C27)*(C8:C27>J5))
E8	E8	=ISODD(C8:C27)
F8:F27	F8	=ISODD(+C8:C27)
D8:D27	D8	=ISODD(C8)
Dynamic array formulas.

Hi DRSTEELE
This worked perfectly.
I spent all day trying different combinations of formulas, so thank you, I have learnt something new for today.

 

[DRSteele]

Excellent! You're welcome.