SUMPRODUCT with an OR component

[andy62]

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

 

[T. Valko]

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))

 

[andy62]

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$3447)+(G448=G$3:G447)+(H448=H$3:H447)+(I448=I$3:I447)+(Q448=Q$3:Q447)+(R448=R$3:R447)+(S448=S$3:S447)+(T448=T$3:T447)+(W448=W$3:W447)+(X448=X$3:X447)+(Y448=Y$3:Y447)+(AO448=AO$3:AO447)+(AQ448=AQ$3:AQ447)+(AR448=AR$3:AR447)+(AS448=AS$3:AS447)+(AT448=AT$3:AT447)+(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)+(BE448=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)+(BP448=BP$3:BP447)+(BQ448=BQ$3:BQ447))<38)

 

[T. Valko]

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.

 

[andy62]

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.