OR Function with more than 30 arguements

J

James T

Hi all,

The following is the function that I currently have:

=IF(OR(M134<>0,M135<>0,M138<>0,M139<>0,M142<>0,M143<>0,M146<>0,M147<>0,M150<>0,M151<>0,M154<>0,M155<>0,M158<>0,M159<>0,M162<>0,M163<>0,M166<>0,M167<>0,M170<>0,M171<>0,M174<>0,M175<>0,M178<>0,M179<>0),1,0)

However I would like the function to be able to cover more than 30 OR
arguements, for example:

'=IF(OR(M134<>0,M135<>0,M138<>0,M139<>0,M142<>0,M143<>0,M146<>0,M147<>0,M150<>0,M151<>0,M154<>0,M155<>0,M158<>0,M159<>0,M162<>0,M163<>0,M166<>0,M167<>0,M170<>0,M171<>0,M174<>0,M175<>0,M178<>0,M179<>0,H134<>0,H135<>0,H138<>0,H139<>0,H142<>0,H143<>0,H146<>0,H147<>0,H150<>0,H151<>0,H154<>0,H155<>0,H158<>0,H159<>0,H162<>0,H163<>0,H166<>0,H167<>0,H170<>0,H171<>0,H174<>0,H175<>0,H178<>0,H179<>0),1,0)

Any ideas on how I could achieve this??

Thanks in advance.

Regards

James
 
D

Domenic

Try...

=(SUMPRODUCT(--ISNUMBER(MATCH(MOD(ROW(H134:H179)-ROW(H134),4),{0,1},0)),(
H134:H179<>0)+(M134:M179<>0))>0)+0

Hope this helps!
 
P

Peo Sjoblom

One way

=SUMPRODUCT(--(ROUND(MOD(ROW(M134:M500),4)/3,0)=1),--(M134:M500<>0))

you can extend that as much as you want as long as you are using the same
patter with 2 pairs of cells checked, 2 not checked, 2 checked and so on


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
P

PinkFloyd201

The following is the function that I currently have:
=IF(OR(M134<>0,M135<>0,M138<>0,M139<>0,M142<>0,M143<>0,M146<>0,M147<>0,M150<>0,M151<>0,M154<>0,M155<>0,M158<>0,M159<>0,M162<>0,M163<>0,M166<>0,M167<>0,M170<>0,M171<>0,M174<>0,M175<>0,M178<>0,M179<>0),1,0)

However I would like the function to be able to cover more than 30 OR
arguements, for example:

'=IF(OR(M134<>0,M135<>0,M138<>0,M139<>0,M142<>0,M143<>0,M146<>0,M147<>0,M150<>0,M151<>0,M154<>0,M155<>0,M158<>0,M159<>0,M162<>0,M163<>0,M166<>0,M167<>0,M170<>0,M171<>0,M174<>0,M175<>0,M178<>0,M179<>0,H134<>0,H135<>0,H138<>0,H139<>0,H142<>0,H143<>0,H146<>0,H147<>0,H150<>0,H151<>0,H154<>0,H155<>0,H158<>0,H159<>0,H162<>0,H163<>0,H166<>0,H167<>0,H170<>0,H171<>0,H174<>0,H175<>0,H178<>0,H179<>0),1,0)

Any ideas on how I could achieve this??
Click to expand...

So if any of those cells are not equal to zero, you want a 1? And 0 if
none are not equal to zero? Why don't you just use:
=SUM(IF(M134:M179<>0,1,0))
 
Top