If and or

J

jamalhakem

Hi
I have a range of cells that could contain numbers (from d11 to
ac11), if and countif function in the next cell to count the numbers
meeting the criteria. This range could be blank (empty cell).
The function returns 0 number if they meet the criteria or they are
blank.
The zero could be because of the criteria or because the cells are
empty.
I want to write this in the next cell but it does not work:
=IF(AND(D11:AD11="";AE11=0);"notpass";IF(AND(0>AE11<=3);"pass";IF(OR(ISNUMBER(D11:AD11);AE11=0);"fine";"")))
Why this because some ranges can be blanks, and some ranges could have
numbers but not in all the cells.
Thanks in Advance
Jam
 
S

Sean Timmons

OK, A few things we can talk about here.

1) 0>AE11<=3 doesn't work in Excel. AND(0>AE11,AE11<=3) is the corrected
version.

2) need to use , not ; to separate your criteria.

3)Using the ISNUMBER will return TRUE only if all values are numbers. Not
sure if you wanted that.
 
D

David Biddulph

Sean's point 2 may not be valid in this case. Some regional settings use a
semi-colon as a list separator, particularly if a comma is used as a decimal
point.
 
S

Sean Timmons

I am too obviously American. :)

David Biddulph said:
Sean's point 2 may not be valid in this case. Some regional settings use a
semi-colon as a list separator, particularly if a comma is used as a decimal
point.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top