1. Computing
Excel Spreadsheet AND Function
Excel AND Function
© Ted French

The AND function is another of Excel's Logical Functions. In general, a logical function only gives you a true or false answer. It evaluates the data in a spreadsheet to see if it meets the specified criteria. If it does, the function returns a True response in the cell where the function is located. If not, a False statement is returned.

In Excel 2007 the AND function allows you to specify up to 255 criteria to be evaluated. All of these criteria would need to meet the specified criteria for the function to give you a True response.

In Excel, the AND function is often combined with other functions, such as the IF function to greatly increase the capabilities of your spreadsheet.

To find out the details on using this function, read this short tutorial on the Excel AND Function.

Related Articles

Comments
January 12, 2010 at 4:18 am
(1) Sang Po says:

Sir,
this is function I tried to used.

IF(AND(E15>8000,E1512000,E1515000,300,0)))

The result I want is.

1. I need 100 for amount between 8,000 14,000
2. I need 200 for the amount between 13,000 and 16,000
3. I need 300 for the amount above 16,000

But i get error with above functions

August 9, 2010 at 4:26 pm
(2) xclmedia says:

I like to use AND Function, OR

January 10, 2011 at 11:56 am
(3) valerie D. says:

To Sang Po:

In your situation, it would be best to use an IF function ranther than an AND function. My suggestion is as follows:

=IF(E15>16000, 300,IF(E15<13000, 100, 200))

Basically, if the value in E15 is greater than 16000 you will get 300, otherwise, it will check if the value is less than 13000, which will give you 100, and if neither are true it means you are in between and you will get a value of 200.

July 2, 2011 at 6:26 am
(4) Keith says:

This will eliminate the ‘100′ response from any number below 8000

=IF(Z3216000,300,IF(Z32<13000,100,200)))

January 28, 2012 at 9:54 pm
(5) Bordet Aning says:

Hi I need help regarding my issue

I have a break log data and i want to see how the number of lates per employee.

ex.

Employee Date Remarks
Joe 12/12/12 Good
Doug 12/12/12 Late
Tracey 12/12/12 Late
Joe 12/13/12 Good
Doug 12/13/12 Late
Tracey 12/13/12 Good

I want an output that looks like this

Employee Number of Lates
Joe 0
Doug 2
Tracey 1

July 25, 2012 at 2:16 am
(6) RJ says:

Hi i need a help in excel.
I have a document log wherein i have different column for revision of document with submitted date in them & final status column which shows status of that document. I need to put in a formula which will show the age of document for reviewing. the problem is i have different revision for documents with different dates. if the final status shows “UR” – “under review” then it should check for which revision it is & the date document submitted. the output should be the no. of days for review.

August 30, 2012 at 3:37 pm
(7) AJAY says:

HOW TO ADD
A+B=A
A=A+B

August 30, 2012 at 3:40 pm
(8) AJAY says:

HOW TO ADD

A+B=A
A=A+B

I.E. 5+2=7 A=5 B=2 STORE IN A=7
IF WE CHANGE VALUE OF B =3
THEN SUM SHOULD BE A=10
10=7+3

September 3, 2012 at 7:18 am
(9) sam says:

what if i want to use if and and function how does it work ?? for example, based on Man, teenager and senior, if he’s a senior and he has more than $1000 in his bank account, “rich” should be the outcome in the designated cell, and if he’s a teenager and he has an amount between $200 and $500 in his bank account “Moderate” should be the outcome….and so on like multiple IF AND formula!! how does that work

September 6, 2012 at 4:11 am
(10) San says:

Dear Sir/Madam,

Grateful if you could tell mehow I could find unit prices for specific items under specific purchase order no.

Say for example how I could find unit prices for the following : -
PO # Item No
PO-0002 678901
PO-0003 890123
from the following table : -
PO # Item No Qty unit price
PO-0001 123456 100 1
PO-0001 234567 200 2
PO-0001 345678 300 3
PO-0002 456789 400 4
PO-0002 567890 500 5
PO-0002 678901 600 6
PO-0003 789012 700 7
PO-0003 890123 800 8
PO-0003 901234 900 9
Looking forward to receiving your advice soonest.

Thanks & Rgds,
San

October 26, 2012 at 4:57 pm
(11) Joe says:

Not sure how to make this happen in excel. Pretty sure I should use the “IF” function.

I would like the cell to read “Master” if value is >4

I would like the cell to read “AA” if value is 3.5,

” ” ” “A” if value is 3, and

” ” ” “B” if value is <3.

How do I do this?

Thanks.

January 22, 2013 at 3:08 pm
(12) Sincerelygrateful says:

I have a document that I am using on my job. I did not create, but it has been used, saved, and sent many times. This matrix is needed to track the employees training and allow my trainer on first glance to see whose training is either 1) updated, 2) expired, or 3) current. Each status is color coated, but for some reason, most employees are showing the expired status when thier training is current or even recently updated.
Can you help?

The formula being used is :
=DAYS360(K2,NOW()+10)

March 3, 2013 at 10:03 pm
(13) SANJAY KUMAR SAHU says:

ANS OF COMENT 1:- IF(AND(B55>=16000),300,IF(AND(B55>=13000,B55=8000,B55<13000),100,0)))

April 23, 2013 at 8:10 pm
(14) JohnD says:

I’m trying to spread a column of payments to vendors horizontally over time, over many columns of months. With what I just learned here, I was able to create:
if date of payment is less than date above, amount is zero and if date of payment is less than or equal to next month, then zero, otherwise the amount paid.
=IF(A17=M1,0,C17))

but I recall in Lotus that there was a way to write:
if date is greater than above AND less than next month, then the amount, otherwise zero.
How do I write that?
It’s something like:
=IF(A17>=L1 AND <M1,C17,0)

where
A17 – the date of payment
C17 – the amount of payment
L1 – a date, e.g. April 1
M1 – the next month, e.g. May 1

Thanx!
JohnD

December 28, 2013 at 8:25 am
(15) Rajesh says:

This is very useful for me. Thanks for this

January 14, 2014 at 3:49 am
(16) ulfat says:

i am working on the GL so there is a problem on the balance side, the credit is 10,000 USD and i have 5 to 10 other figures on the debit side, so now i want to use a formula to see the balance ,
any one can help please
thanks
ulfat

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>

©2014 About.com. All rights reserved.