SumProduct with AND inside....deos not work

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

Guest

The below is my function. I have sumproduct with and inside, trying to use two conditions for the particular counterpart in the sumproduct. I am getting #Value# as my result, so something must be wrong

any help appreciated...Thank
Meggi

=IF((experiment!$R$2:$R$49624=X$1),SUMPRODUCT(--(experiment!$K$2:$K$49624<=S11),--(experiment!$O$2:$O$49624>=S12))+SUMPRODUCT(--(experiment!$K$2:$K$49624>S11),--(experiment!$O$2:$O$49624<S12))+SUMPRODUCT(--(experiment!$K$2:$K$49624<S11),--AND(experiment!$O$2:$O$49624<S12,(experiment!$O$2:$O$49624>S11)))+SUMPRODUCT(--AND(experiment!$K$2:$K$49624>S11,(experiment!$K$2:$K$49624<S12)),--(experiment!$O$2:$O$49624>S12)),0)
 
Maybe it would be easier if explain what you are trying to do instead?
For instance you can't use IF in that way, it will only return TRUE if
the first cell =X1 and you can't use AND that way either.

--

Regards,

Peo Sjoblom

Meggie said:
The below is my function. I have sumproduct with and inside, trying to use
Click to expand...
two conditions for the particular counterpart in the sumproduct. I am
getting #Value# as my result, so something must be wrong!
any help appreciated...Thanks
Meggie
Click to expand...
=IF((experiment!$R$2:$R$49624=X$1),SUMPRODUCT(--(experiment!$K$2:$K$49624<=S
11),--(experiment!$O$2:$O$49624>=S12))+SUMPRODUCT(--(experiment!$K$2:$K$4962
4>S11),--(experiment!$O$2:$O$49624<S12))+SUMPRODUCT(--(experiment!$K$2:$K$49
624<S11),--AND(experiment!$O$2:$O$49624<S12,(experiment!$O$2:$O$49624>S11)))
+SUMPRODUCT(--AND(experiment!$K$2:$K$49624>S11,(experiment!$K$2:$K$49624<S12
)),--(experiment!$O$2:$O$49624>S12)),0)
 
If you are trying to do logical combinations of conditons, use * for AND
and + for OR.

Jerry
 
Back
Top