# More than 30 logical expressions in AND -- Quandry

#### sunny316

##### New Member
Hello,

I have a formula which goes something like this

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",AND(A35="N",A36="N",A37="N",A38="N",A39="N",A40="N",A41="N",A42="N", A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N")),"ERROR: NOT identified !", ""))

now I wanted to expand it to

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",A35="N",A36="N",A37="N",A38="N",A39="N",A40="N",AND(A41="N",A42="N",A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N",A52="N",A53="N",A54="N",A55="N",A56="N",A57="N",A58="N",A59="N",A60="N")),"ERROR: Prohibited substance mismatch or NOT identified !", ""))

and it didn't work, says that formula is too long. I tried it this way

=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF((D7="Yes") =AND(A18="N",A19="N",A20="N",A21="N",A22="N",A23="N",A24="N",A25="N",A26="N",A27="N",A28="N",A29="N",A30="N",A31="N",A32="N",A33="N",A34="N",A35="N",A36="N",A37="N",A38="N",A39="N",A40="N")=AND(A41="N",A42="N",A43="N",A44="N",A45="N",A46="N",A47="N",A48="N",A49="N",A50="N",A51="N",A52="N",A53="N",A54="N",A55="N",A56="N",A57="N",A58="N",A59="N",A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !", ""))

but that didn't give me the expected result.

I'm using Office 2003, please help me with getting around the stupid limitation that Microsoft placed on using just 30 expressions in a function in MS Excel 2003.

Thanks..appreciate any help.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### DonkeyOte

##### MrExcel MVP
Perhaps you might try:

=IF(D7="Yes/No?","Select Yes or No on Instruction Sheet",IF(AND(D7="Yes",COUNTIF(A18:A60,"<>N")=0),"ERROR: Prohibited substance mismatch or NOT identified !",""))

##### MrExcel MVP
Concerning the first formula:

What do you want to return if D7 is Yes and all cells in A18:A51 are N?

#### jim may

##### Well-known Member
Do you mean something like this? Commit to cell with ControlKey+ShiftKey+Enter,
not just Enter. {}'s should appear around below formula; {}'s cannot be entered by using keyboard.

Code:
``=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF(AND(D7="Yes",A18:A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !",""))``

#### sunny316

##### New Member

Do you mean something like this? Commit to cell with ControlKey+ShiftKey+Enter,
not just Enter. {}'s should appear around below formula; {}'s cannot be entered by using keyboard.

Code:
``=IF(D7="Yes/No?","Select Yes or No on Instruction sheet", IF(AND(D7="Yes",A18:A60="N"),"ERROR: Prohibited substance mismatch or NOT identified !",""))``

I am not aware of the {} option, can you please explain what it is and would it work any differently than the usual AND function in Excel 2003?

##### MrExcel MVP

I don't think an array formula is needed here...

#### DonkeyOte

##### MrExcel MVP
Have you tried the COUNTIF suggestion ? No real need for an Array in this instance (at least not as I see it).

#### sunny316

##### New Member
Thanks all, I'll try both the COUNTIF and the Array options and see which works best for my sheet.

#### shemayisroel

##### Well-known Member
I don't think an array formula is needed here...

Replies
10
Views
395
Replies
9
Views
131
Replies
4
Views
49
Replies
4
Views
433
Replies
12
Views
899