Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with an OR component
I Have a SUMPRODUCT function which is working fine to return a count of rows
meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this function I need to add a component which returns TRUE if at least one of another dozen or so conditions are TRUE. So it's like putting in another dozen sets, all wrapped in an OR function. I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" and then array-entering the whole SUMPRODUCT function, but that doesn't work. Any ideas? TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with an OR component
I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)"
To use an "or" condition: --((B1:B4000<1)+(C1:C4000<)0) Or: SIGN((B1:B4000<1)+(C1:C4000<)) if at least one of another dozen or so conditions are TRUE. A dozen? That's a lot! -- Biff Microsoft Excel MVP "andy62" wrote in message ... I Have a SUMPRODUCT function which is working fine to return a count of rows meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this function I need to add a component which returns TRUE if at least one of another dozen or so conditions are TRUE. So it's like putting in another dozen sets, all wrapped in an OR function. I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" and then array-entering the whole SUMPRODUCT function, but that doesn't work. Any ideas? TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with an OR component
Ooops! Typos:
--((B1:B4000<1)+(C1:C4000<)0) SIGN((B1:B4000<1)+(C1:C4000<)) Should be: --((B1:B4000<1)+(C1:C4000<0)0) SIGN((B1:B4000<1)+(C1:C4000<0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" To use an "or" condition: --((B1:B4000<1)+(C1:C4000<)0) Or: SIGN((B1:B4000<1)+(C1:C4000<)) if at least one of another dozen or so conditions are TRUE. A dozen? That's a lot! -- Biff Microsoft Excel MVP "andy62" wrote in message ... I Have a SUMPRODUCT function which is working fine to return a count of rows meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this function I need to add a component which returns TRUE if at least one of another dozen or so conditions are TRUE. So it's like putting in another dozen sets, all wrapped in an OR function. I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" and then array-entering the whole SUMPRODUCT function, but that doesn't work. Any ideas? TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with an OR component
Hi Biff - Thanks, that works perfectly. I lied about the dozen conditions;
it's actually 38 (I'm surprised I don't get the dreaded "Formula too long"): --(((B448=B$3:B447)+(C448=C$3:C447)+(D448=D$3:D447)+ (G448=G$3:G447)+(H448=H$3:H447)+(I448=I$3:I447)+(Q 448=Q$3:Q447)+(R448=R$3:R447)+(S448=S$3:S447)+(T44 8=T$3:T447)+(W448=W$3:W447)+(X448=X$3:X447)+(Y448= Y$3:Y447)+(AO448=AO$3:AO447)+(AQ448=AQ$3:AQ447)+(A R448=AR$3:AR447)+(AS448=AS$3:AS447)+(AT448=AT$3:AT 447)+(AU448=AU$3:AU447)+(AV448=AV$3:AV447)+(AW448= AW$3:AW447)+(AX448=AX$3:AX447)+(AY448=AY$3:AY447)+ (AZ448=AZ$3:AZ447)+(BA448=BA$3:BA447)+(BB448=BB$3: BB447)+(BC448=BC$3:BC447)+(BD448=BD$3:BD447)+(BE44 8=BE$3:BE447)+(BI448=BI$3:BI447)+(BJ448=BJ$3:BJ447 )+(BK448=BK$3:BK447)+(BL448=BL$3:BL447)+(BM448=BM$ 3:BM447)+(BN448=BN$3:BN447)+(BO448=BO$3:BO447)+(BP 448=BP$3:BP447)+(BQ448=BQ$3:BQ447))<38) "T. Valko" wrote: I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" To use an "or" condition: --((B1:B4000<1)+(C1:C4000<)0) Or: SIGN((B1:B4000<1)+(C1:C4000<)) if at least one of another dozen or so conditions are TRUE. A dozen? That's a lot! -- Biff Microsoft Excel MVP "andy62" wrote in message ... I Have a SUMPRODUCT function which is working fine to return a count of rows meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this function I need to add a component which returns TRUE if at least one of another dozen or so conditions are TRUE. So it's like putting in another dozen sets, all wrapped in an OR function. I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" and then array-entering the whole SUMPRODUCT function, but that doesn't work. Any ideas? TIA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with an OR component
Yikes!
Can't really tell what you're trying to do with this but I see that's not all a contiguous range so I'm not sure if that can be cleaned up somehow. -- Biff Microsoft Excel MVP "andy62" wrote in message ... Hi Biff - Thanks, that works perfectly. I lied about the dozen conditions; it's actually 38 (I'm surprised I don't get the dreaded "Formula too long"): --(((B448=B$3:B447)+(C448=C$3:C447)+(D448=D$3:D447)+ (G448=G$3:G447)+(H448=H$3:H447)+(I448=I$3:I447)+(Q 448=Q$3:Q447)+(R448=R$3:R447)+(S448=S$3:S447)+(T44 8=T$3:T447)+(W448=W$3:W447)+(X448=X$3:X447)+(Y448= Y$3:Y447)+(AO448=AO$3:AO447)+(AQ448=AQ$3:AQ447)+(A R448=AR$3:AR447)+(AS448=AS$3:AS447)+(AT448=AT$3:AT 447)+(AU448=AU$3:AU447)+(AV448=AV$3:AV447)+(AW448= AW$3:AW447)+(AX448=AX$3:AX447)+(AY448=AY$3:AY447)+ (AZ448=AZ$3:AZ447)+(BA448=BA$3:BA447)+(BB448=BB$3: BB447)+(BC448=BC$3:BC447)+(BD448=BD$3:BD447)+(BE44 8=BE$3:BE447)+(BI448=BI$3:BI447)+(BJ448=BJ$3:BJ447 )+(BK448=BK$3:BK447)+(BL448=BL$3:BL447)+(BM448=BM$ 3:BM447)+(BN448=BN$3:BN447)+(BO448=BO$3:BO447)+(BP 448=BP$3:BP447)+(BQ448=BQ$3:BQ447))<38) "T. Valko" wrote: I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" To use an "or" condition: --((B1:B4000<1)+(C1:C4000<)0) Or: SIGN((B1:B4000<1)+(C1:C4000<)) if at least one of another dozen or so conditions are TRUE. A dozen? That's a lot! -- Biff Microsoft Excel MVP "andy62" wrote in message ... I Have a SUMPRODUCT function which is working fine to return a count of rows meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this function I need to add a component which returns TRUE if at least one of another dozen or so conditions are TRUE. So it's like putting in another dozen sets, all wrapped in an OR function. I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" and then array-entering the whole SUMPRODUCT function, but that doesn't work. Any ideas? TIA |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with an OR component
My original post with "C1:C4000" was hypothetical. The real funtion gets
"copied down" and the endpoint of the array ranges are not fixed (e.g., I$3:I447), so the range varies depending on where you copy the formula to. The criteria are also a moving target; essentially they are checking to see whether a value in the current row (e.g., 448) matches any values in any prior rows (3-447), same column. The OR piece you helped me with returns a 0 if all 38 match, or a 1 if at least one doesn't match. Hopefully the extra explanation helps anyone who might want to borrow this code in the future. Thanks again. "T. Valko" wrote: Yikes! Can't really tell what you're trying to do with this but I see that's not all a contiguous range so I'm not sure if that can be cleaned up somehow. -- Biff Microsoft Excel MVP "andy62" wrote in message ... Hi Biff - Thanks, that works perfectly. I lied about the dozen conditions; it's actually 38 (I'm surprised I don't get the dreaded "Formula too long"): --(((B448=B$3:B447)+(C448=C$3:C447)+(D448=D$3:D447)+ (G448=G$3:G447)+(H448=H$3:H447)+(I448=I$3:I447)+(Q 448=Q$3:Q447)+(R448=R$3:R447)+(S448=S$3:S447)+(T44 8=T$3:T447)+(W448=W$3:W447)+(X448=X$3:X447)+(Y448= Y$3:Y447)+(AO448=AO$3:AO447)+(AQ448=AQ$3:AQ447)+(A R448=AR$3:AR447)+(AS448=AS$3:AS447)+(AT448=AT$3:AT 447)+(AU448=AU$3:AU447)+(AV448=AV$3:AV447)+(AW448= AW$3:AW447)+(AX448=AX$3:AX447)+(AY448=AY$3:AY447)+ (AZ448=AZ$3:AZ447)+(BA448=BA$3:BA447)+(BB448=BB$3: BB447)+(BC448=BC$3:BC447)+(BD448=BD$3:BD447)+(BE44 8=BE$3:BE447)+(BI448=BI$3:BI447)+(BJ448=BJ$3:BJ447 )+(BK448=BK$3:BK447)+(BL448=BL$3:BL447)+(BM448=BM$ 3:BM447)+(BN448=BN$3:BN447)+(BO448=BO$3:BO447)+(BP 448=BP$3:BP447)+(BQ448=BQ$3:BQ447))<38) "T. Valko" wrote: I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" To use an "or" condition: --((B1:B4000<1)+(C1:C4000<)0) Or: SIGN((B1:B4000<1)+(C1:C4000<)) if at least one of another dozen or so conditions are TRUE. A dozen? That's a lot! -- Biff Microsoft Excel MVP "andy62" wrote in message ... I Have a SUMPRODUCT function which is working fine to return a count of rows meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this function I need to add a component which returns TRUE if at least one of another dozen or so conditions are TRUE. So it's like putting in another dozen sets, all wrapped in an OR function. I tried variations of "--(OR(B1:B4000<1,C1:C4000<0, etc.)" and then array-entering the whole SUMPRODUCT function, but that doesn't work. Any ideas? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Missing Component (Converter) | Charts and Charting in Excel | |||
can i run a principal component analysis | Excel Discussion (Misc queries) | |||
Removing Tax Component | Excel Discussion (Misc queries) | |||
Office web component . | Excel Worksheet Functions | |||
Web Excel Component Bug | Excel Discussion (Misc queries) |