Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Product Quest: include an OR?
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", |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Product Quest: include an OR?
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", |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Product Quest: include an OR?
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", |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Product Quest: include an OR?
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", |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Product Quest: include an OR?
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(SUBS TITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDE X(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$ AX$1168,"X"),0))),$W$1,0))), IF($BX$150,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$1 73:$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 "Miguel Zapico" wrote: 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", |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have two columns (A) and (G) that have like product codes however | Excel Worksheet Functions | |||
Counting Rows/Columns for Copying Formulas | Excel Discussion (Misc queries) | |||
Percentages | Charts and Charting in Excel | |||
How to set a formula to count the product appear how manytime | Excel Worksheet Functions | |||
Which function(s)? | Excel Worksheet Functions |