On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time. Continue reading
Comments on: IF AND formula in Excel
by Svetlana Cheusheva, updated on
Comments page 11. Total comments: 490
I have this Data how to solve?
Grade>=17 And Sex =F And Location it is Bhandup, Mulund, 10% on Basic, Grade>19, 20% on basic , otherwise 8%
Hi Ravina,
Condition 1 and Condition 2 seem to be overlapping in your task. However, the following formula may serve as a starting point and can easily be modified if you decide to alter the conditions:
=IFS(B2>19,20,AND(B2>=17,C2="F",D2="Bhandup, Mulund"),10,TRUE,8)
PS If you wonder why there is ‘TRUE’ before 8 there, press ‘F1’ to search for the Excel IFS function, and you will find the answer. That’s where I learnt it myself.
Hi Everyone! I'm building an estimating spread sheet and I'm having "#Value!" issues with what should be a very simple process. In my case Columns A & B contain a drop down menu. In column A there is only one appropriate choice, let's say "Floor". In column 2 there can be up to three appropriate choices, "Bathroom", "Ensuite" & "PWD" from which, obviously, only one is chosen. In simple English the process is this:
IF cell A1 = "Floor" & cell B1 = "Ensuite", multiply cells F1*G1*O1, otherwise return "" blank.
Of course cell B1 might read "Bathroom, "Ensuite" or "PWD" so this needs to be included in the formula. My problem appears to be incorporating these 3 variables for that cell and I can't find a way around it. Any help that you can give would be greatly appreciated!
Many thanks,
Dick
=IF(AND(A1="FLOOR",OR(B1="BATHROOM",B1="ENSUITE",B1="PWD")),F1*G1*O1,"")
That one should work
=IF( orand(A2>89 , A2<199) , "a"; IF( A2=1, "2"; IF( A2=200, "ok" ; " no " )))
I have this data:
KEY A B C D E F G H I J
3323 6 66 86 64 20 89 68 42 16 31
3324 5 17 46 36 9 40 72 62 81 68
3325 62 8 44 18 80 52 6 55 3 66
3484 37 29 31 67 57 55 2 50 12 28
3485 32 33 49 80 29 77 30 18 68 78
3486 59 11 55 41 62 71 72 70 1 68
I WANT TO USE THE IF AND FORMULA TO CHECK IF I GET 2 NUMBER THAT ARE THE SAME THEN
I WANT TO THE KEY AND THAT NUMBERS. FOR EXAMPLE KEY 3323 HAS 6, 66 SO IS KEY 3325 SO THERE IS A MATCH. THE OUTPUT WILL BE 3323 6 66 86 64 20 89 68 42 16 31
3525 62 8 44 18 80 52 6 55 3 66
ANOTHER MATCH IS 3324 5 17 46 36 9 40 72 62 81 68
3486 59 11 55 41 62 71 72 70 1 68
WE HAVE 6-66 AS A MATCH IN 3323 AND 3525 AND ANOTHER MATCH IN 72-68 IN 3324 AND 3486
I ALSO WANT TO USE THE VLOOKUP OR ANY OTHER FORMULA A THAT WILL DO THE SEARCH AND A MATCH. I AM A BEGINNER, AND STILL LEARNING, ANY HELP OUT THERE?
Hi
I'm trying to achieve what I believe the be a IF AND OR formula please. This is what I have so far:
=IF((AND(B3="linux/Unix",D5="Run the following network scan:")),"nmap -A ","1..255 | % {echo ''192.168.X.$_''; ping -n 1 -w 100 192.168.X.$_} | Select-String ttl")
B3 is a drop box so if "Linux/Unix" is not selected, then Windows is the only other option which displays the second outcome.
However, I want to add an extra piece =if(D5="Next Question", " ", " "). Basically I want a black cell left if D5="Next Question" regardless of what B3 equals please.
I have tried adding this to the end with extra brackets, but this does not work and I'm not sure where else to nest it please?
=IF(D5="NEXT QUESTION","",IF(AND(B3="linux/Unix",D5="Run the following network scan:"),"nmap -A ","1..255 | % {echo ''192.168.X.$_''; ping -n 1 -w 100 192.168.X.$_} | Select-String ttl"))
That should work
Pls help me get a formulae for calculating the rate(%) for a particular deposit given the amount and period using the below tables:
Fixed deposit of 10 to 50M. Rate;
30 to 90 days - 6.50%
91 to 180 days - 7.00%
181 to 365 days - 7.25%
Fixed deposit of >50M. Rate;
30 to 90 days - 7.00%
91 to 180 days - 7.25%
181 to 365 days - 7.50%
what if im looking for an IF THEN in a range,
example
IF in this range "x" is found THEN do this
Can you please give a correct formula for the below
IF SHEET2 COLUMN B VALUE = SHEET1 COLUMN B VALUE AND (ITS) SHEET1 COLUMN D VALUE= POSTED OR HOLD THEN COLUMN C VALUE ADDED TO COLUMN H
IF SHEET2 COLUMN B VALUE = SHEET1 COLUMN B VALUE AND (ITS) SHEET1 COLUMN D VALUE= DELIVERED OR TRANSIT THEN COLUMN C VALUE ADDED TO I
I have 2 scenario's which have 2 diff IF statements. I need this to be in the same cell and cannot get it to work. Any suggestions.
IF(AND(J4="MOLCO",Y4="SHORE",W4>S4),"-",S4-W4)
IF(AND(J4="MOLOO",Y4="SHORE",W4>U4),"-",U4-W4)
In both statements if all the AND(.....) are true then it only needs to return "-".
Thanks
Michael
=IF(AND(J4="MOLCO",Y4="SHORE",W4>S4),"-",IF(OR(J4"MOLCO",Y4"SHORE",W4U4),"-",IF(AND(J4"MOLOO",Y4"SHORE",W4<U4),U4-W4,""))))
It's a little messy but, this should work.
IF(OR(J4"MOLCO",Y4"SHORE",W4<U4)
The formula is missing "" back to back. Not sure why it won't show up.
Less than "" back-to-back.
so, for my homework im supposed to do this:
In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:
a. Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7 equals 0 and the Total in cell B8 is greater than 20000.
b. If the logical test is true, display Yes (using “Yes” for the value_if_true argument).
c. If the logical test is false, insert a nested IF function.
d. Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
e. If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
f. If the logical test is false, display No (using “No” for the value_if_false argument).
but i cant get it to work. i tried making this formula:
=IF(AND(B7=0,B8>20000),Yes,IF(AND(B7>0,B8>200000),Yes,No))
but it only gives a result of #NAME?
you can try this
=IF(AND(A7=0,B7>20000),"Yes",IF(AND(A7>0,B7>200000),"No"))
=IF(AND(L11>=30, L11<100), "OBESE", IF(AND(L11=25), "OVERWEIGHT", IF(AND(L11>25, L11>=18.5), "NORMAL WEIGHT", IF(AND(L110), "UNDERWEIGHT")))))
Hi Patrick,
If I get it right, you mean the following:
1) an ‘OBESE’ note is required if the value in L11 is equal to or more than 30 but less than 100;
2) an ‘OVERWEIGHT’ note is required if the value in L11 is equal to 25;
3) a ‘NORMAL WEIGHT’ note is required if the value in L11 is equal to or more than 18.5 but less than 25;
4) an ‘UNDERWEIGHT’ note is required if the value in L11 is equal to 0.
If so, I would rewrite your formula applying the IFS function:
=IFS(L11=0, "UNDERWEIGHT", AND(L11>=18.5, L11<25), "NORMAL WEIGHT", L11=25, "OVERWEIGHT", AND(L11>=30, L11<100), "OBESE")
Please note that the above formula will bring ‘#N/A’ every time you try to make it process values like 15, 27, or 102 since they are not covered by your conditions.
Can someone tell me what's wrong with this formula, I can't seem to get it to work. Thanks!
=IF(AND(L11>0, L11=18.5, L11=25, L11=30, L11<100, "Obese"))))))))
That's not the formula I wrote, hang on...
Im looking for a formula that works for this:
=IF(AND(B6="yes",OR(C6>7am,C6<3pm)),"yes","no")
I have tried a combination of if, and and or together and =time(7,0.0) and it only returns the first logical test. Im losing sleep over it!
If cell b6 = yes
And
Cell c6 is less than 7am or greater than 3pm
Return yes value.
IF A is greater than 1000 and lesser than 2000 then figure should display of "A" CAN YOU SHARE THE FORMULA FOR THE SAME
I am trying the following formula and i get an error, can you help?
=IF(LEFT(D28,2)="SS",MID(D28,5,1)&"X",LEFT(D28,2),IF(OR(LEFT(D28,2)="OJ",AND(F28="1113"),"OJ1")))
my input looks like this, in K i would like to see OJ1, when D is OJ and F is 1113. the first part of the nested commands works fine.
D F K
OJDS01 1113 OJ1
SSDSC1 CX
i want formula for the followings:
1.ifSalary Rs.7000 =Lessthan than salary <7000*8.33%
2.7001 to 21000 salary (533.33/31days * no of worked days.
3.above 21000 =(833.33/31days* no.of.worked days.
kindly help me.
I've created a conduit fill schedule where if you select a conduit type the conduit type and the conduit size you get the conduit area number associated with that specific conduit type and trade size. In cell G, you can choose the conduit type via a drop down list. In cell F you enter the conduit size. In cell K depending on the selected conduit type (cell G) and the conduit size (cell F) you get the conduit area number for (cell G) & (cell F). My conduit types are PVC 40,PVC 80,RMT,EMT, & LFMC. When I combine IF with Vlookup in cell K the conduit area number is for PVC 40 only, the cell doesn't recognize the other conduit types.
How do I solve this?
Thank You for your expertise.
Hi,
I am trying to use match formula to give me the row where a formula applies too 10 cells. Eg if the cell number stays bellow 4.5 for 10 cells then give me the row this happens in. But how would i write 10 cells. As so far i have match(value, array, match type) but i need to put in only if applies to the following 10 cells.
Any thoughts?
the moving ribbon on the bottom of the blog page is crap, cant read because of that stupid thing
I have a list of customers each with a unique ID number
I can have the same customer multiple times on a report
Each Customer has a status of Ordered or Ordering
Using the customer number I need to know which customer number has both "Ordered" & "Ordering" in its status field.
The Result should be a Yes or No.
or to go simpler
COL A COL B COL C COL D
ROW 1 Customer ID Ordering Ordered Both
ROW 2 1111 yes yes yes
IN D2 put the following formula: =IF(AND(B2="yes",C2="yes"),"yes","no")
then just simply copy and paste it down the Column D
:)
Hi Rich,
Let’s say your customers’ ID numbers are in Column A and the information on their status, which is either ‘Ordered’ or ‘Ordering’, is in Column C. I would suggest applying Ablebits’ ‘Merge Duplicates Wizard’ first to keep only unique ID numbers in Column A and a summed up record saying ‘Ordered;Ordering’ or ‘Ordering;Ordered’ in Column C. Don’t omit the ‘Backup this worksheet’ option so as not to lose your original table. Then I would write the following formula in Cell, say, H21:
=IF(AND(H20=INDEX(A:A,MATCH(H20,A:A,0)),OR(INDEX(C:C,MATCH(H20,A:A,0))="Ordered;Ordering",INDEX(C:C,MATCH(H20,A:A,0))="Ordering;Ordered")),"Yes","No")
That formula reacts to changing an ID number in Cell H20 and brings either “Yes” or “No”, depending on the situation. If you try to type in the ID number which Column A does not contain, the formula will bring ‘#N/A’.
I have the following scenario which I can't seem to get the IF(OR(AND to work. I have an employee database that lists the name, title and location. I am using the above formula to determine security groups and distribution groups. I have come across a scenario where I have multiple titles with multiple possible locations. I currently have the following formula, which comes back with findings but they are inaccurate. =IF(OR(B3="OCT TECH - OCT Technician",B3="TECH - Ophthalmic Technician",B3="SCRIBE - Ophthalmic Scribe",B3="CLMANG - Clinic Manager",B3="OPTAST - Ophthalmic Assistant",B3="VFTECH - Visual Field Technician",B3="SPTEST - Special Testing Technician",B3="SXCORD - Surgical Coordinator",B3="DOCTOR - Medical Doctor",B3="OPTOM - Optometrist")*AND(C2="HAGER - Hagerstown",C2="Fred - Frederick",C2="HANCO - Hancock",C2="MARTI - Martinsburg"),"YES","NO") Any ideas? Thank you
currently have =IF(AND(F3>=D5,F3<=E5),”X”,””). This is checking to see if F3 is between two dates and if so to put an X in the cell otherwise leave blank. I want to add to that formula IF F3 is 1 day past E2 (which is a end date of project), or is 2 days past E2, then put a W. How might I accomplish this?
what is nested if function
I'm trying to use the IF statement to produce a value for three different cells that are equal to use that equal number. if they are not equal use the lowest value of the three.
ex.
IF('TRAINING TRACKER'!E3='TRAINING TRACKER'!M3='TRAINING TRACKER'!W3,"count#","count lowest#")
What do I need to use to produce a numeric value for each instance?
Hi there!! This is really long, but I am looking for a formula that would handle all of this
If column E = Eagle and column D = Select 6 and column P = 0-75 then column Q = 1.5%
If column E = Eagle and column D = Select 6 and column P = 76-80 then column Q = 1.1%
If column E = Eagle and column D = Select 6 and column P = 81-85 then column Q = .75%
If column E = Eagle and column D = Platinum 5 or Platinum 7 and column P = 0-75 then column Q = .75%
If column E = Eagle and column D = Platinum 5 or Platinum 7 and column P = 76-80 then column Q = .50%
If column E = Eagle and column D = Platinum 5 or Platinum 7 and column P = 81-85 then column Q = .38%
If column E = Symetra and column D = Edge GPS 5 or Edge GPS 7 then column Q = 1%
If column E = Symetra and column D = Advantage Income, Custom 5, Custom 7, Select 5 or Select 7 then column Q = .5%
This is very nice lesion. thank for teaching us. I have a question as below.
Below is the formulas I use as an example, but I got one error with the latest formulas, please assist to let me know what happened on this or because using wrong.
=IF(AND(B4<=25000,C4<=45),"Med N1",IF(AND(B4=46),"Med N3",IF(AND(B4=46),"Med N4",IF(AND(B4>=50001,C4=50001,C4>=46),"Med N5",IF(AND(B4>=100000,C4=100001, C4>=46),"Med N6","")))))))
I would like to ask why this below condition not apply?
IF(AND(B4>=100001, C4>=46),"Med N6","")
Help, how can I do a formula for the following condition: I have cells B3-D6 which will have an "x" (columns) as reply to four questions (rows). I want the fifth row to automatically add the" x" to be the total of the "X" only if it equals to four or three x's in a column. If a column equals 2 or less, nothing should be done. I would truly appreciate any assistance. Thanks.
i need a help in excel which can identify 2 different set of line example 1 Tin - Arrow gold - 5 K Seeds - 7400 Rs 1 Pkt - Broccoli Saki - pkt - 510 " this is in single cell but i want to highlight the same pls advice
Dear Sir,
I would like count no of cells having specific text for a specific employee with drop down list excluding blank cells.Here we are giving work sheet with data for your reference.
S.NO Employee Name FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT
26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
1 M.SANKAR G G G G G G G G G G G G A G G G G G A G G G G G
FABRICATOR B½ B G½
2 K.MURALI G G G G G G G G G G G G G G G G G G G G G
WELDER B½
3 M.VENKATRAMAIH G G G G G G G G G G G G G G G G A G G G G G G
WELDER B½ G½
4 M.RAMACHANDRA G G G G G G G G G G G G G G G G G A G G G G
KALASI B½ G½ B½
Thanks & regards,
Ramesh
Hi, good day
How can I horizontally lookup rate from a table (using index match) with 2 conditions.
This is my data set
Name 30-Nov-17 03-Jan-18 01-May-18 31-Oct-18
Andy 10% 8% 10% 8%
Sarah 10% 8% 10% 8%
James 10% 8% 10% 8%
John 10% 8% 10% 8%
I want to pick out the rates from the table based on Name and Effective Date. Eg., James / 15-Jan-18 would return "8%".
Many thanks
Thanks, svetlana.
for sharing.
Thanks for your great work you do by helping others..
I have a unique problem seeking formula for use in Office 2010.
Problem: Looking for a formula to :- Find matches in any two cells in the same row where.. For example column A has exactly five or 8 digit numbers and column B has only a single digit with result in column c stating match/no match
Say.. i have two columns in Excel each having numbers.In the first column A i have 5 or 8 number digits exactly. in B i have a single digit. what i need to do is find if the number present in column B, is matching in column A with result in column c stating match/no match i have enjoyed your formula given "Example 2. Find matches in any two cells in the same row
If you are looking for a way to compare columns for any two or more cells with the same values within the same row, use an IF formula with an OR statement: =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "") " ...... But not helping with my problem Please reply. thanks
How to solve this?