SUMPRODUCT with not equals (<>)

G

Guest

Hello

This is attempt # 2 for me.. I have the following equation, which does not work. I am trying to count the number in the entrie list for the the first two conditions, that are not equal to X1,Y1, or Z1.. ..Should I be using ORs or ANDs. I repeat this formulas for all 49624 cells, where the S cells increment by one. I have alot of data and am looking for how much of the data is not the standard (x,y,z

=SUMPRODUCT((experiment!\$K\$2:\$K\$49624<\$S9)*(experiment!\$O\$2:\$O\$49624>\$S8)*(experiment!\$R\$2:\$R\$49624<>\$X\$1)*(experiment!\$R\$2:\$R\$49624<>\$Y\$1)*(experiment!\$R\$2:\$R\$49624<>\$Z\$1)

Any help would be grea

Thank
Meggie

A

Arvi Laanemets

Hi

Maybe
=SUMPRODUCT((experiment!\$K\$2:\$K\$49624<\$S9)*(experiment!\$O\$2:\$O\$49624>\$S8)*(e
xperiment!\$R\$2:\$R\$49624<>\${X\$1,\$Y\$1,\$Z\$1}))

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

Meggie said:
Hello,

This is attempt # 2 for me.. I have the following equation, which does not
work. I am trying to count the number in the entrie list for the the first
two conditions, that are not equal to X1,Y1, or Z1.. ..Should I be using ORs
or ANDs. I repeat this formulas for all 49624 cells, where the S cells
increment by one. I have alot of data and am looking for how much of the
data is not the standard (x,y,z)=SUMPRODUCT((experiment!\$K\$2:\$K\$49624<\$S9)*(experiment!\$O\$2:\$O\$49624>\$S8)*(e

B

Bob Phillips

=SUMPRODUCT((experiment!\$K\$2:\$K\$49624<\$S9)*(experiment!\$O\$2:\$O\$49624>\$S8)*(e
xperiment!\$R\$2:\$R\$49624<>\$X\$1)*(experiment!\$R\$2:\$R\$49624<>\$Y\$1)*(experiment!
\$R\$2:\$R\$49624<>\$Z\$1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Meggie said:
Hello,

This is attempt # 2 for me.. I have the following equation, which does not
work. I am trying to count the number in the entrie list for the the first
two conditions, that are not equal to X1,Y1, or Z1.. ..Should I be using ORs
or ANDs. I repeat this formulas for all 49624 cells, where the S cells
increment by one. I have alot of data and am looking for how much of the
data is not the standard (x,y,z)=SUMPRODUCT((experiment!\$K\$2:\$K\$49624<\$S9)*(experiment!\$O\$2:\$O\$49624>\$S8)*(e

A

Almost 50,000 cells...

=SUMPRODUCT(--(experiment!\$K\$2:\$K\$49624<\$S9),--(experiment!\$O\$2:\$O\$49624>\$S8
),--ISNA(MATCH(experiment!\$R\$2:\$R\$49624,\$X\$1:\$Z\$1,0)))

This will count the empty cells in R-range also in if any.

Meggie said:
Hello,

This is attempt # 2 for me.. I have the following equation, which does not
work. I am trying to count the number in the entrie list for the the first
two conditions, that are not equal to X1,Y1, or Z1.. ..Should I be using ORs
or ANDs. I repeat this formulas for all 49624 cells, where the S cells
increment by one. I have alot of data and am looking for how much of the
data is not the standard (x,y,z)=SUMPRODUCT((experiment!\$K\$2:\$K\$49624<\$S9)*(experiment!\$O\$2:\$O\$49624>\$S8)*(e