ISODD in COUNTIFS statement

[pistonbroke]

Hello all. I am trying to use the ISODD worksheet function in a COUNTIFS statement, on a range of cells with two additional criteria, but cannot get the Odd Number check to work.

=COUNTIFS(R6C48:R503C48,"<"&R2C43,R6C47:R503C47,"=0", R6C53:R503C53,"ISODD")

So counting cells when 3 criteria are TRUE: Value less than a constant in a cell | Value equal to 0 in a column of the same row | Value in rows of another column are ODD numbers.

I probably have the ISODD Syntax incorrect, i also tried the MOD function with 2 as the divisor, but couldn't make that work either.

Any ideas appreciated - thank you for taking the time to have a look at this all.

Thanks, Kevin

 

[DRSteele]

Ya, you can't put ISODD into the criteria argument in the COUNTIFS function. Try this after reading the reference. Note that the + has to be there in the ISODD argument.

=SUMPRODUCT((RC648:RC50348<RC243),(RC647:RC50347=0),(ISODD(+RC653:RC50353)))

references: Excel formula: Count cells that contain odd numbers | Exceljet

 

[pistonbroke]

DRSteele, thanks for the reply. I may not have correctly indicated the ranges i am working with, so attached is a snapshot. The formula as you wrote for me did not work likely because ranges were incorrect

Column 47 is check for Zero; Col 48 is check for less than Row 2 Col 43 value; Col 53 is check if value is ODD.. The check range is Rows 6 to 503. In the small section shown here, rows 10 and 12 should both Count 1 (odd #'s in Col 53 and other criteria true), others would return Zero.

Thankyou again, Kevin

 

[DRSteele]

Please upload sample data from a simple spreadsheet. See my signature below for a link to the Forum's app.

 

[pistonbroke]

2022 Drafting Summary V1.xlsm
	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD
1			Max Green	Min Yellow							Green Race Lap Shifting (Count of Upshifts per Lap)
2		Enter Max Green Laptime:	35.00	35.0005		All Laps	Green	Yello & RS	Check		No Shifts	1 Per Lap	2 Per Lap	3 Per Lap	4 Per Lap	Odd #
3		(Sanity Check for Lap Green Math Counter)				245	176	69	Ok		0	0	0	173	0	0
4							Green Flag Laps	Calc Lap Time	NGear	NGear	Luke ID Shift Laps	Luke ID Shift Laps	Luke ID Shift Laps	Corr Speed [mph]	Corr Speed [mph]	PcLoad [%]
5							Range	Max	Min	Max	Min	Max	Range	Min	Max	Min
6	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 1	00:36.1	1	36.10	2	5	0	0	0			0.5
7	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 2	00:34.3	0	34.27	3	5	0	6	6			0.6
8	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 3	00:34.1	0	34.10	3	5	6	12	6			0.5
9	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 4	00:34.2	0	34.18	3	5	12	18	6			0.6
10	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 5	00:34.7	0	34.71	3	5	18	24	7			0.7
11	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 6	00:34.6	0	34.58	3	5	24	30	6			0.6
12	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 7	00:34.2	0	34.23	3	5	30	36	5			0.7
13	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 8	00:34.1	0	34.05	3	5	36	42	6			0.5
14	A Driver	A Racecar	6/5/2022, 06	4:58:45 AM	Lap 9	00:34.1	0	34.09	3	5	42	48	6			0.6
2022 Gateway R15 Car 45

Cell Formulas
Range	Formula
AR2	AR2	=AQ2+0.0005
AT3	AT3	=COUNTA(AT6:AT503)
AU3	AU3	=COUNTIFS($AV$6:$AV$503,"<"&$AR$2,$AU$6:$AU$503,"=0")
AV3	AV3	=COUNTIFS($AV$6:$AV$503,">"&$AQ$2,$AU$6:$AU$503,"=1")
AW3	AW3	=IF((AU3+AV3)<>AT3,"Wrong","Ok")
AY3	AY3	=COUNTIFS($AV$6:$AV$503,"<"&$AQ$2,$AU$6:$AU$503,"=0", $BA$6:$BA$503,"=0")
AZ3	AZ3	=COUNTIFS($AV$6:$AV$503,"<"&$AQ$2,$AU$6:$AU$503,"=0", $BA$6:$BA$503,"=2")
BA3	BA3	=COUNTIFS($AV$6:$AV$503,"<"&$AQ$2,$AU$6:$AU$503,"=0", $BA$6:$BA$503,"=4")
BB3	BB3	=COUNTIFS($AV$6:$AV$503,"<"&$AQ$2,$AU$6:$AU$503,"=0", $BA$6:$BA$503,"=6")
BC3	BC3	=COUNTIFS($AV$6:$AV$503,"<"&$AQ$2,$AU$6:$AU$503,"=0", $BA$6:$BA$503,"=8")
BD3	BD3	=SUMPRODUCT(($AV$6:$AV$503<$AQ$2),($AU$6:$AU$503=0),(ISODD(+$BA$6:$BA$503)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AS6:CB509	Cell	contains a blank value	text	NO
AW3	Cell Value	contains "Wrong"	text	NO
AS6:CB508	Expression	=$AV6<=$AQ$2	text	NO
AS6:CB509	Expression	=$AV6>$AQ$2	text	NO

 

[pistonbroke]

I don't know why it lined out rows below Row 4 ?? I selected rows 1 to 14

 

[pistonbroke]

OK, this worked... after reading that help on the Function, i used * (multiplier) in place of commas to seperate the Arrays and force 1 or 0 in place of true and false for the count to work (apparently two neagtive symbols do tyhe same but didnt work for me) !

Thankyou DRSteele for the pointers and help...

=SUMPRODUCT((R6C48:R503C48<R2C43)*(R6C47:R503C47=0)*(ISODD(+R6C53:R503C53)))

Great Board here - thanks all who took a look...

 

[Fluff]

I don't know why it lined out rows below Row 4 ?? I selected rows 1 to 14

I suspect that you accidentally edited the BB code using strike-through. I have corrected it for you.