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.

"nastech" wrote:

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?

"Miguel Zapico" wrote:

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.

"nastech" wrote:

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",