Is it possible to have a range in an AND statement?

1. Is it possible to have a range in an AND statement?

Hi All

I have a formula that I need to examine a range of criteria using IF & AND to check results. I have several columns and can create multiple AND logical criteria but this takes a huge amount of time as there are over 30 columns, and will grow to many more.

The formula I would use and know works is:

=if(and(b1="No",b2="No"),"Not Started",In Progress")

b1 onwards could eventually reach in excess of b100. (RSI City)! )

What I though I would do is

=if(and(b1:b100="No"),"Not Started","In Progress")

but this always returns a #value! error.

Probably something simple but can't get the darn thing to work.

2. Re: Is it possible to have a range in an AND statement?

You can enter your formula as CSE (array formula, using Control+Shift+Enter rather than just enter) and it works.

3. Re: Is it possible to have a range in an AND statement?

Try this...

=IF(COUNTIF(B1:B100,"No"),"Not Started","In Progress")

4. Re: Is it possible to have a range in an AND statement?

This worked perfectly. Thanks Haripopuri

Know it would be something simple !

5. Re: Is it possible to have a range in an AND statement?

Not Array.....

=IF(COUNTIF(B1:B100,"No")=ROWS(B1:B100),"Not Started","In Progress")

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1