Calculating a sumproduct with AND and <>s

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have use the following formul

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

Which has been successful.. I have three similar to this, just changing to Y$1 and X$1, in lieu of Z$1

I know what to calculate all of those that do not equal $Y$1,$Z$1, and $X$1. I tried the following formula and it does not work. Please help. Should I use an and or what

=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 great!
Thank
meggie
 
One way:

Say your SUMPRODUCT formulae were in A1:A3. Then,

A4: = 49623 - SUM(A1:A3)

or, if you want to ignore blanks:

A4: = COUNTA(experiment!$R$2:$R$49624) - SUM(A1:A3)
 
Back
Top