Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 158
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 158
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 158
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Missing Component (Converter) Rusty_Pylate Charts and Charting in Excel 1 May 17th 09 09:54 AM
can i run a principal component analysis yujosamo Excel Discussion (Misc queries) 1 February 5th 09 01:26 AM
Removing Tax Component Browny Excel Discussion (Misc queries) 4 August 22nd 08 12:08 AM
Office web component . Ermias Excel Worksheet Functions 0 May 20th 08 11:40 PM
Web Excel Component Bug [email protected] Excel Discussion (Misc queries) 1 May 11th 07 12:22 AM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"