AND is WAY too long

[jtidfold]

Hello there:

I have a psychotically long "AND" formula in an "IF" formula that I would LOVE to shorten up. It's analyzing 10 cells and it's doing this on 1,800 rows so it's really killing my processing time lol

Here's what "AND" part is now: = IF ( AND (A1 = "VALUE", A2 = "VALUE", etc)

I tried shortening it to = IF ( A1:A10="VALUE") but it errored out on me.

Am I stuck by the limitations of the "AND" formula on this one or is there a shortcut?

As always, the help is much appreciated

Cheers
KD

 

[Gerald Higgins]

Perhaps
=if(a1&a2="ValueValue",
but I'm not sure if that will be an improvement . . .

 

[jtidfold]

It might not be - I should've done this to begin with though so it's on me here.

Here's what I'm trying to analyze. I have to see if I can find a serial number in 6 lists, and if I can't I have to show that result. If its in any of the 6 lists, then I don't have to worry about it. The "AND" formula in column P is the killer for me.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Serial No	"S" Serial Number	No "S" Serial Number	On List 1 with "S"	On List 1 with No "S"	On List 2 With "S"	On List 2 With No "S"	On List 3 with "S"	On List 3 With No "S"	On List 4 With "S"	On List 4 With No "S"	On List 5 With "S"	On List 5 With No "S"	On List 6 With "S"	On List 6 With No "S"	On any list (Y/N)?
2	S123456789	S123456789	123456789	NO	NO	NO	NO	NO	NO	NO	NO	YES	NO	NO	NO	YES
Analysis

 

[Andrew Poulsom]

How about?

=IF(COUNTIF(D2:O2,"NO")=COLUMNS(D2:O2),"NO","YES")

 

[Gerald Higgins]

Or maybe

=countif(d2:o2,"YES")>0,"YES","NO")

 

[jtidfold]

You guys are awesome - I always get the help I need here and it's MUCH appreciated.

I went with Gerald's only to avoid the array - I don't know how much it saves on the processing but every little bit helps.

Thanks again!!!
KD