SUMPRODUCT with not equals (<>)

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
Click to expand...
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
 
=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
Click to expand...
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
 
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
Click to expand...
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
 
Back
Top