Sum Product Quest: include an OR?

[Guest]

hi, am wondering how to modify formula to include an OR item.. thanks

I put the IF prefix, seems to be working..
=IF(AND(ROW()>$A$540,ROW()<$A$567),"",
SUMPRODUCT(--(LEFT($AX$56:$AX$1168,1)="x"),--($G$56:$G$1168<>"x")))

would like to OR the last part:
--($G$56:$G$1168<>"x")

with: $G$7="x",

 

[Guest]

You cannot use OR inside a SUMPRODUCT formula as you are having it, as the
SUMPRODUCT expects arrays and the OR returns a single value.
From your explanation, if the contents of cell G7 are "x", the last part of
the SUMPRODUCT should not be taken into account. You can acomplish this with
an IF statement, something like:
=IF(AND(ROW()>$A$540,ROW()<$A$567),"",IF($G$7="x",SUMPRODUCT(--(LEFT($AX$56:$AX$1168,1)="x")),SUMPRODUCT(--(LEFT($AX$56:$AX$1168,1)="x"),--($G$56:$G$1168<>"x"))))

Hope this helps,
Miguel.

 

[Guest]

thankyou, the part you wrote seems to work; but even though my 1st IF
statement did not blow up on me, it does not seem to be working.

Is that the way I should try to exclude 1 or more ranges of rows: with the
ROW< / > items?

 

[Guest]

The ROW formulas that you have check if the current cell is located between
the value of cell A540 and cell A567. If your intention is to skip that rows
on the SUMPRODUCT, you can split the formula in two, one for each branch, and
add them. Something like:
=IF($G$7="x",SUMPRODUCT(--(LEFT($AX$56:$AX$540,1)="x"))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x")),SUMPRODUCT(--(LEFT($AX$56:$AX$540,1)="x"),--($G$56:$G$540<>"x")))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x"),--($G$567:$G$1168<>"x"))

Hope this helps,
Miguel.

 

[Guest]

thankyou very much for all your help.. if you were the same person answering
on previous questions, trying to figure out what I was doing.. ?

might not have known how to ask, suppose could have asked:


ANSWER TO: Find Error result in column, where calculated (pick a start
point & exclude a range), Hyperlink to it (with row number as friendly name
in hyperlink)

=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),$W$1,0))),
IF($BX$15>0,IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),"-"))


Quantity of Errors:
=IF($G$7="x",SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x")),
SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"),--($G$173:$G$540<>"x")))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x"),--($G$567:$G$1168<>"x"))

SAMPLE: Test for errors:
=IF(AND(S9<>".",OR($G$7="x",G9<>"x"),OR(CP9={"",0})),"X",





XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX