formula help please

A

A. Toczko

Hello. In a spreadsheet, I want to count how many rows meet the following
conditions:
column D=apple and (column E=5 or column E=6 or column E=7 or column E=8).
Thank you!
 
R

Ron Rosenfeld

Hello. In a spreadsheet, I want to count how many rows meet the following
conditions:
column D=apple and (column E=5 or column E=6 or column E=7 or column E=8).
Thank you!
Click to expand...

If it does not need to be the ENTIRE column, then:

=SUMPRODUCT((D1:D100="apple")*(E1:E100={5,6,7,8}))

Adjust the range D1:D100 and E1:E100 to what is necessary.




--ron
 
R

Ron Coderre

Try this:

F1: =SUMPRODUCT(--((E1:E1000)={5,6,7,8})*(D1:D1000="apple"))
(Note: Commit that formula by just pressing [Enter].

If 1000 rows isn't enough, just change that reference.

Does that hellp?
 
B

Bob Phillips

=SUMPRODUCT(--(D1:D100="apple"),--((E1:E100=5)+(E1:E100=6)+(E1:E100=7)+(E1:E
10
0=8)))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

or ...

=SUMPRODUCT((D1:D10="apple")*(E1:E10={5,6,7,8}))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

Aladin Akyurek

A. Toczko said:
Hello. In a spreadsheet, I want to count how many rows meet the following
conditions:
column D=apple and (column E=5 or column E=6 or column E=7 or column E=8).
Thank you!
Click to expand...

Definitely faster to OR with the IsNumber|Match idiom:

=SUMPRODUCT(--($D$2:$D$400="Apple"),--ISNUMBER(MATCH($E$2:$E$400,{5,6,7,8},0)))
 
Top