Formula - Odd or Even Numbers in a Range of Cells

P

Phuoc

Well-known Member

Joined

Apr 29, 2016

Messages

989

Office Version

1. 2021

• Yesterday at 9:18 AM

• #2

Try this

Book1
	A	B	C
1
2	2	2	3
3	2
4	3
5	4
6	55
7	6
8	6
9	7
10	7
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(ISODD(+A2:A9)*ISEVEN(+A3:A10))
C2	C2	=SUMPRODUCT(ISEVEN(+A2:A9)*ISODD(+A3:A10))

 

Upvote 0

Trevor G

Well-known Member

Joined

Jul 17, 2008

Messages

6,768

Office Version

1. 365
2. 2021
3. 2019
4. 2016

Platform

1. Windows

• Yesterday at 9:27 AM

• #3

You could also use Sumproduct with MOD take a look here. I like Phuoc suggestion also but not sure if 2010 provides a formula ISODD or ISEVEN

 

Upvote 0

S

ststern45

Well-known Member

Joined

Sep 17, 2005

Messages

969

Office Version

1. 365
2. 2010

Platform

1. Windows

• Yesterday at 9:50 AM

• #4

Thank you for the replies.

I made an error in the opening post.
Example 1: The total would be 1 odd since the value 22 is even in cell A2

33
22
99
55
11
99
22
44
11
44

 

Upvote 0

S

ststern45

Well-known Member

Joined

Sep 17, 2005

Messages

969

Office Version

1. 365
2. 2010

Platform

1. Windows

• Yesterday at 9:51 AM

• #5

Example 2: The total would be 7 odd (A1:A7) since the value 22 is even in cell A8.

77
77
55
55
11
55
99
22
22
44

 

Upvote 0

Cubist

Well-known Member

Joined

Oct 5, 2023

Messages

1,175

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Yesterday at 10:13 AM

• #6

How about this?

Book1
	A	B	C	D	E	F	G
1	Example 1	# even before first odd	# odd before first even		Example 2	# even before first odd	# odd before first even
2	33	0	1		77	0	7
3	22				77
4	99				55
5	55				55
6	11				11
7	99				55
8	22				99
9	44				22
10	11				44
11	44
Sheet10

Cell Formulas
Range	Formula
B2,F2	B2	=MATCH(TRUE,ISODD(+A2:A11),0)-1
C2,G2	C2	=MATCH(TRUE,ISEVEN(+A2:A11),0)-1

 

Upvote 0

Solution

S

ststern45

Well-known Member

Joined

Sep 17, 2005

Messages

969

Office Version

1. 365
2. 2010

Platform

1. Windows

• Yesterday at 10:30 AM

• #7

Excellent. Thank you very much.
Appreciate all your help.

 

Upvote 0