For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading
Comments on: Excel IF statement with multiple conditions
by Svetlana Cheusheva, updated on
Comments page 50. Total comments: 4557
Hi, I really need some help please.
I need the end result to be Yes or No.
For yes, the criteria should be:
F2 must be greater than 2
G2 must be False
S2 must be False
Q2 must not contain the words Matter Data
=IF((AND(F2>2,G2="False",S2="False",Q2Matter Data)),"Yes","No")
Hello Ami!
If I understand your task correctly, the following formula should work for you:
=IF(AND(F2>2,G2="FALSE",S2="FALSE", NOT(ISNUMBER(FIND("Matter Data",Q2,1)))),"Yes","No")
Read how to use the IF function with text values here.
I hope this will help, otherwise please do not hesitate to contact me anytime.
=IF((AND(F2>2,G2="False",S2="False",Q2Matter Data)),"Yes","No")
...Sorry, slight typo but still doesn't work :(
Hi i have question regarding multiple condition:How to get system stock May'20 column by applying function IF.please suggest
example:
Customer ; Stock to be consumed; Schedule May'20 ;System stock (May'20)
B 602 560 42
C 545 YTR −
D 1 0 1
E 27 0 27
G 120 150 30
H 36 YTR -
Thanks in advance
Hello!
You have not specified which system stock you want to receive on May 20 - general or by customer. But in any case, I recommend using the SUMIFS function. Read more in this Excel SUMIFS guide.
I hope this will help, otherwise please do not hesitate to contact me anytime.
if 1-2 range answer multiplies by 2,500
3-4 multiplies by 3,000
5-6 multiplies by 4,500
what is the formular
Hello Bridget!
If I understand your task correctly, the following formula should work for you:
=IFERROR(CHOOSE(R1,2500,2500,3000,3000,4500,4500)*Q1,Q1)
or
=IF(OR(R1=1,R1=2),Q1*2500,IF(OR(R1=3,R1=4),Q1*3000,IF(OR(R1=5,R1=6),Q1*4500,Q1)))
Hope this is what you need.
I got help with a formula last month and need to add some additional variables to it. typed at end of my comment is the formula i have so far, but need to also add somewhere into the below "if AJ6 = CONUS AND D6 IS NOT 12, AND I6 IS "D", then return 10 and if I6 is "C", then return 12" everything else below remains the same. I'm not sure if this is even possible.
=IF(Aj6="Conus",IF(AND(D6=12, SUM(IF(G6={9820580,159384},1,0))=1),17, IF(OR(D6 < 12,AND(D6=12,G6=6620363)),15,"")), IF(AJ6="Oconus",28,""))
Hello Jessica!
Replace the "" symbol in your formula
=IF(AH1="Conus", IF(AND(D1=12,SUM(IF(G1={9820580,159384},1,0))=1),17, IF(OR(D1 < 12,AND(D1=12,G1=6620363)),15,"")), IF(AH1="Oconus",28,""))
with these conditions:
=IF(AND(AJ6="Conus",D6<>12,I6="D"), 10,IF(I6="C",12,""))
The result is a new formula:
=IF(AH1="Conus", IF(AND(D1=12,SUM(IF(G1={9820580,159384},1,0))=1),17, IF(OR(D1 < 12,AND(D1=12,G1=6620363)),15,"")), IF(AH1="Oconus",28,IF(AND(AJ6="Conus",D6<>12,I6="D"),10, IF(I6="C",12,""))
))
I hope it’ll be helpful.
That didn't work :( "C" and "D" values in column I for CONUS are still returning 15 instead of 12 and 10
what if i concatenate the ranking (ei. A,B,C,D) with Conus or Oconus. Then i would just need a formula that says:
if A1=ACONUS or BCONUS and D1 is anything but 12, return 15
if A1=CCONUS and D1 is anything but 12, return 12
if A1=DCONUS and D1 is anything but 12 return 10
if ACONUS, BCONUS, CCONUS, DCONUS and D1=12, return 17
if A1= AOCONUS or BOCONUS, return 34
if A1 = COCONUS or DOCONUS, return 32
Hi,
I'm having a problem with writing the formula for the following with 3 conditions:
-if column A says yes , and the value in column B=80 then I need to multiply Value in B by 0.30
if column A says no , then I need to multiply Value in B by 0.45
Your help will be much appreciated
Hello Aneta,
Please try the following formula:
=IF(AND(A1="Yes",B1=80), B1*0.3,IF(A1="No",B1*0.45,B1))
I hope it’ll be helpful.
hi ,
Thank you, that's amazing it works , I have one last question.
I need to write the formula for the following but the one I did below it does not work:
=IFS(AND(G9="yes",F9=80),F9*0.3),IF(G9="no",F9*0.45)
Here are 3 conditions:
1.IF G9 says yes and F9=80, theN multiply F9*0.30
3.if G9 says no, then multiply F9 * 0.45
Your help would be much appreciated. Thank you Alexander
Hello Aneta!
Why are you asking the same question twice? Just change the cell addresses in the formula above.
Sorry, I'm not sure what happened there, I have one extra condition to enter
IF G9 says yes and F9 I greater or equal to 80 , then I need to multiply F9*0.30, if G9 says yes and F9 is lower than 80 then multiply F9* 0.15, if G9 says yes then multiply F9 by 0.45
My goal is to know when to Water my lawn. I am trying to nest and or in an if function.
So on even days if the week day is Monday, Tuesday Friday or Saturday then I water.
I got the formula to work but on August first - - it switched to odd days.
this is the formula I used in the first cell for June 1st =IF(AND(OR(WEEKDAY(A1,1)=2,WEEKDAY(A1,1)=3,WEEKDAY(A1,1)=6,WEEKDAY(A1,1)=7), AND(MOD(A1,2)=0)), "Water", " ") --- Where did I go wrong?
6/1 Monday
6/2 Tuesday Water
6/3 Wednesday
6/4 Thursday
6/5 Friday
6/6 Saturday Water
6/7 Sunday
6/8 Monday Water
6/9 Tuesday
6/10 Wednesday
6/11 Thursday
6/12 Friday Water
6/13 Saturday
6/14 Sunday
6/15 Monday
6/16 Tuesday Water
6/17 Wednesday
6/18 Thursday
6/19 Friday
6/20 Saturday Water
6/21 Sunday
6/22 Monday Water
6/23 Tuesday
6/24 Wednesday
6/25 Thursday
6/26 Friday Water
6/27 Saturday
6/28 Sunday
6/29 Monday
how can i make a formula using grading system (5,4,3,2,1) in date submission
5 for earlier than due date by 2 or more days
4 for earlier than due date by 1 day
3 for due date
2 for later than due date by 1 day
1 for later than due date by 2 or more days
this is my sample formula i made, but seems not to get the grade 2 or somethings wrong i think, pls help
=IF(H17<=F17-2,"5",IF(H17F17,"2",IF(H17>=F17,"1")))))
Hello Melvin!
If I understand your task correctly, the following formula should work for you:
=IF((F17-H17) >=2,5,IF((F17-H17) >=1,4,IF(F17=H17,3, IF((F17-H17)>-2,2,1))))
Hope this is what you need.
in excel formula if a1 is between 1 to 5 then multiple 2.5 and if A1 is between 6 to 9 then multiply from 2
Hello Sydney!
If I understand your task correctly, the following formula should work for you:
=IF(AND(A1 > 1,A1 < 5),A1*2.5,IF(AND(A1 > 6,A1 < 9),A1*2,A1))
I hope it’ll be helpful.
Dear Alex,
Thank you so much. It answered my question and its perfectly working.
Hello,
Looking to use conditional formatting to turn a row Green if the word "TEST" is in any cell in that row twice
Row should turn RED if the word test is only there once
Thank You!
Hello Mindy!
Use conditional formatting as described in these manuals.
https://www.ablebits.com/office-addins-blog/relative-absolute-cell-references-excel-conditional-formatting/
https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/
Apply the condition formula
=COUNTIF($A50:$Z50,"TEST")>1
I hope this will help
Meaning, two cells in that row contain the word TEST
I'm so stuck!! I'm as blank as the sky !!
if A3 has "New" and B3 has "CT" then put value of H3 in Cell? ... But with the drop down I need
if A3 has "New" and B3 has "SUS" then put value of H4 in Cell?
This Is probably simple but i'm tired but need it :( Thank guys n Girls :)
Hello David!
If I understand your task correctly, the following formula should work for you:
=IF(AND(A3="New",B3="CT"),H3, IF(AND(A3="New",B3="SYS"),H4,0))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hello devid,
I am trying to calculate a cell is eligible or not .like if a cell contain yes,or date then and then he is eligible otherwise no..how to create formula
Need help with a formula !
My cell contains the following value (95% Cotton, 5% Elastane)
I need to set a condition that if my cell has the value "cotton" and the percentage before the string(cotton) is >= 50% it should return true if not it should return false.
Here are a few samples of how the values could be populated.
90% Cotton, 8% Polyamide, 2% Elastane
95% Baumwolle (Bio), 5% Elasthan
90% Cotton, 8% Polyamide, 2% Elastane
In whatever scenario if the cell has value with "cotton" in it and the value before the string is >= 50% , it should return true.
Thank you
Hello Mustafa!
If I understand your task correctly, the following formula should work for you:
=IF(IFERROR(IFERROR(MID(A10,SEARCH("Cotton",A10,1)-4,2), MID(A10,SEARCH("Cotton",A10,1)-3,1)),0) >= 50,TRUE,FALSE)
I hope this will help, otherwise please do not hesitate to contact me anytime.
If month is 202005 or 202006, and staus is hiring, then vlookup based on business name else 0
if(or('Org moves'!CO2=202005,'Org moves'!CO2202006) And('Org moves'!CP2="Hiring"),vlookup(Walk!B7,'Org moves'!AB:CQ,68,0),"0")
i used this. please help resolving error
Hello!
I could not check your formula on real data. Try this formula
=IF(AND(OR('Org moves'!CO2=202005,'Org moves'!CO2=202006), ('Org moves'!CP2="Hiring")), VLOOKUP(B7,'Org moves'!AB:CQ,68,0),"0")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Could you help with this data validation custom formula? This formula works:
=OR(D10="X", D10="B") but when I add an additional condition, it doesn't work. My new conditions are:
CONDITION 1: D10="X" OR
CONDITION 2: D10="B" AND E10="55"
I have tried several combinations of OR and AND formula including the sample here on the this website but none works for me. I appreciate your help. Thank you very much.
Hello Montey!
If I understand your task correctly, the following formula should work for you:
=IF(OR(D10="X", AND(D10="B",E10=55) ),TRUE,FALSE)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alexander,
It works! I just removed the IF function as my condition needs to be always true to allow data entry in another cell. Thank you very much!
IF(OR(D7="N/A", AND(D7="0", F7="Yes")), "0","1")
I need the value of this formula to be 0 if, either the value of D7 is N/A or if the value of D7=0 AND F7 is Yes.
Am I using the correct formula for this condition?
Hello Sanjay!
The formula is spelled correctly. However, if you do not write numbers as text, then you do not need to use quotation marks.
=IF(OR(D7="N/A", AND(D7=0, F7="Yes")), 0,1)
Thanks Alexander Trifuntov.
It worked. I spent a lot of time on this and finally your suggestion did the trick.
Appreciate the quick turnaround.
=IF(OR(AND(AA2="Canada",Z2="Vancouver"),K2=1,k2=9),M2*5,M2=M2)
how do i solve the problem below using the nested OR & AND function
Increment the backers-count by 5 if:
The launched_at_month is January OR September
The city is Vancouver AND the country_trimmed is Canada
If these conditions are not met, the backers-count stays the same.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(AND(AA2="Canada",Z2="Vancouver", OR(K2=1,K2=9)),M2*5,M2)
I hope it’ll be helpful.
lets says =IF(AND(R4>S4,S4>T4,T4>U4,U4>V4,V4>W4,W4>X4),"UP&DOWN",IF(AND(R4<S4,S4<T4,T4<U4,U4<V4,V4<W4,W4<X4),"PASS","NOT GROWN"))
up&down cannot be used idk why,the circumstance was r4 to w4 just one of them going down its gonna say up&down
please help
Hello!
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Need help with formula
if D3=x,v4>120,"RED", "YELLOW"),If D4="Active", "BLANK")
trying to day if D3 = Active and V4 Greater than 120 then RED otherwise Yellow. But if D3 is not equal to Active then leave it blank.
Thanks
Anna
Hello Anna!
If I understand your task correctly, the following formula should work for you:
=IF(AND(D3="Active",V4 > 120),"RED", IF(D3 <> "Active","","YELLOW"))
I hope it’ll be helpful.
not sure why, but the post is not displaying the entire formula as I am pasting it,
=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)
updated but still not correct
=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)
Hello Keith!
Please describe your problem in more detail. The IFOR function does not exist. What does the B25A16 mean? It’ll help me understand it better and find a solution for you. Thank you.
=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)
I am trying to compare a sales amount in a cell to a commission schedule and based on teh sales figure it will bring multiply the sales times the correct commission %. I know this is not correct but I am not sure where I am going wrong
=IFOR((AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)
Hello,
Just want to say thank you , you doing great job, lord shower grace on you.
I was writing my problen but suddenly answer clicked, thank you again.
Thank you so much!
Hi,
I'm trying to write an "If" statement if 3 variables in different cells match up with each other then "Y" else "N"
If [A2]=[C2]=[D2]THEN "Y" ELSE "N" ENDIF
Hello Michelle!
Please try the following formula
=IF(A2=B2,IF(B2=C2,"Y","N"),"N")
I hope it’ll be helpful.
I'm stuck, wonder if someone can help me:
I need ranges-
if cell value is =1500 and =3000 and =5000 "5000+"
I know it's where I'm putting the () but I can't seem to get it right?
Hello Cindy!
I’m sorry but your task is not entirely clear to me. I hope you have studied the recommendations in the above tutorial. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.
sorry, it didn't type out correctly
If h2 is less than 1500, then "<1500", if h2 is greater than or equal to 1500 AND less than 3000, then "1500-2999", if h2 is greather than or equal to 3000 AND less than 5000, then "3000-4999", if h2 is greater than 5000, then "5000+"
Hello Cindy!
Please try the following formula:
=IF(D2 < 1500,"<1500", IF(D2 < 3000,"1500-2999",IF(D2 < 5000,"3000-4999", "5000+" ) ) )
The second way:
Write your values in columns A, B, C. A1 - 0 B1 - 1499 C1 - <1500 A2 - 1500 B2 - 2999 C2 - 1500-2999, etc. If the final value is written in D1, then the formula for determining the output will be as follows:
= VLOOKUP (D1, A1: C4,3,1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hello,
I'm hoping to add a calculated field to my pivot table and need a little help.
A B C calc field
1 "A only"
1 1 "A & B"
1 "C only"
1 "B only"
1 1 1 "All 3"
1 1 "B & C"
1 1 "A & C"
Hello, how do write a formula for this? If total is between .01%-.99%, output should be $40, if between 1% and 1.99% - output should be $210. I tried IF and also IF(AND with , but I can't get it to work...help please. Thank you so much!
0.01% ---> 0.99% $40.00
1% ---> 1.99% 210.00
2% ---> 2.99% 240.00
3% ---> 3.99% 270.00
4% ---> 4.99% 300.00
5% ---> 5.99% 330.00
6% ---> 6.99% 370.00
7% ---> 7.99% 410.00
8% ---> 8.99% 450.00
9% ---> 9.99% 490.00
10% ---> 10.99% 540.00
11% ---> 11.99% 590.00
12% ---> 12.99% 640.00
13% ---> 13.99% 690.00
14% ---> 14.99% 740.00
hello Phatima!
Write your values in columns A, B, C. A1 - 0.01% B1 - 0.99% C1 - 40.00, etc. If the final value is written in F1, then the formula for determining the output will be as follows:
=VLOOKUP(F1,A1:C15,3,1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi,
Great article!
In my example below, is it possible to add to the formula so that results in column 'C' would also take into consideration the value in column 'B' and (subtract 10 if 'B'=1) or (subtract 20 if 'B'=2)
Hope this makes sense, thank you!
A B C
1 35 1 150
2 33 150
3 40 2 200
4 41 200
=ifs(and(A1>=30,A1=38,A1<42),200)
Hello!
Your formula does not work. I did not understand what result you want to get. If there are already numbers in column C, then they cannot be changed by the Excel formula. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
thanks for the response.
I didn't realize I pasted wrong the formula
here is the correct one I use:
=ifs(and(A1>=30,A1=38,A1=" and "<") from Column 'A' which are size values. Now I want to add additional criteria (Column 'B') which is a quality grade (A,B,C or can be switched to numbers 1,2,3) so the calculated value (price) in column 'C' would be less by 10 if 'B=1', less by 20 if 'B=2', less by 30 if 'B=3', if 'B=blank' leave it as it is.
Hope this makes sense and thank you for all your help!
Alex
ifs(and(A1>=30,A1=38,A1<42),200)
I'll put it in words:
if "A1" is "greater than or equal to" 30 and "less" than 38, than 150, if "A1" is "greater than or equal to" 38 and "less" than 42, than 200
hope this makes sense as it won't let me paste the formula in the chat correctly.
Hello Alex!
If I understand your task correctly, the following formula should work for you:
=IF(B1<>"", (IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,""))) - (B1*10), IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,"")))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Yes! That's it!
Thank you sooooo much!
Appreciate all your help!
ifs(and(A1>=30,A1<38)=38,A1<42),200)
...Never mind, the forum messages will change the formula every time I submit it.
...here it is again, for some reason when I publish the comment it changes the formula pasted.
I'll try again with no "=" sign
ifs(and(A1>=30,A1=38,A1<42),200)
Hi,
I order supplies for my company and have created a sheet that has 13 tabs, one for each 4 week period and a Summary tab at the end. On each tab I have:
ItemOrdered Qty Item# Supplier Cost$ DateOrdered DateReceived QTY/CS
I need to create a formula where IF a certain item is ordered (say triggered by Item#), THEN, the value in the Qty column is tallied as a sum on my Summary tab. Does that make sense?
Thanks in advance!
Matt
Hello,
I need the following formula:
if in column A I have the word "Revision", I need to put it in column B but not at same level (-1 level).
Exemple: if A4=Revision: 2.1--> put all content in B3
Thank you very much
Hello Pascale!
If I understand your task correctly, the following formula should work for you:
=IF(SEARCH("Revision",A4,1)>0,A4,"")
If there is anything else I can help you with, please let me know.
if the 1st row is greater than the other row then the answer is 1
if the 1st row is lesser than the other row then the answer is 0
if the 1st row is 0 than the other row is 0 then the answer is 1
if the 1st row is equivalent to the other row then the answer is 0
Hello Vincent!
I’m sorry but your task is not entirely clear to me. Do you want to compare rows or cells? What does "row is greater than the other row" mean? Could you please describe it in more detail? Thank you!
I am trying to work out an IF formula for the following. I have a spreadsheet that details stock and whether it is in date, out of date or expiring soon. I need the cell to show whether an item expires one month from today, is in date or out of date. I am struggling to work out the IF formula - this is what I have come up with so far, but am unable to get any further. Any help would be greatly appreciated by this newbee to Excel
=IF(E3<TODAY()*AND(-365-335),"Exp 1 mth",IF(E3<TODAY()-365,"Out of date","In date"))
Hello Sarah!
If I understand your task correctly, the following formula should work for you:
=IF(E3>TODAY(),"In date",IF(E3 > EDATE(E3,1),"Exp 1 mth","Out of date"))
I hope this will help, otherwise please do not hesitate to contact me anytime.
HI,
Please suggest how automate in sheet B
Sheet A.
01-05-2020 02-05-2020 03-05-2020
Branch Cash Transfer Cash Transfer Cash Transfer
A 200 90 500 152 5165 2132
B 500 100 700 515 6516 98
C 700 930 785 515 212 19
Sheet B.
Date 02-05-2020
Branch Cash Transfer
A ? ?
B ? ?
C ? ?
Hello Anoop!
I’m sorry but your task is not entirely clear to me.
For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.
WHAT SHOULD BE FORMULAE CONSIDERING BELOW CONDITIONS:
- Cell is greater than equal to 5 = P-HOME
- Cell is less than 5 = HLWP
- Cell is "0" OR "NULL" OR "#N/A" = LWP
Hello!
If I understand your task correctly, the following formula should work for you:
=IFERROR(IF(B1 >= 5,"P-HOME", IF(B1=0,"LVP","HLWP") ),"LWP")
Hope this is what you need.
How do I extend this formula to retrun "Agency" if employer column does not = J Smith Ltd.
Employer Source Fomula
J Smith Ltd Direct =IF(C4="J Smith LTD","Direct")
ABC Recruit Agency
XYZ Recruit Agency
Hello Tim!
If I understand your task correctly, the following formula should work for you:
=IF(ISERROR(SEARCH("J Smith LTD",C4)),"Agency","Direct")
Hope this is what you need.
Gud day Sir, please I have a large document that is up to 20 pages & maybe more. I want the heading which is in row 1,2,3,4,5 to show at the top of each page, I used the print title option and it worked perfectly. Now I want the bottom note which is up to eight rows to show at the bottom of each page. How can I make that work?
Please I need help on that.
Thanks.
Formula is =IF(OR(AND(G16="D",Q16>300000),AND(G16="V",Q16>300000),AND(G16="I",Q16>1000000),AND(G16="ORC",$L$5>600000)),"Y","")
It's returning the first two conditions "D" & "V" correctly but not the last two conditions. What am I missing? Please help. Thank you.
Hello Luisa!
I copied the formula from a blog in Excel and made sure that it works. Write in detail what results you want to get. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Thank you for your reply.
What I'm trying to accomplish has several conditions, so if:
Service column has "D" and total of more than 300K the result should say "Y" for yes.
OR
Service column has "I" and total of more than 300K the result should say "Y" for yes.
OR
Service column has V and total of more than 1M the result should say "Y" for yes.
OR
"H" total column is more than 600K the result should say "Y" for yes.
I hope this is clear explanation. Thank you so much for your help.
Hello Luisa!
Based on your explanations, the formula needed to make very small changes
=IF(OR(AND(G16="D",Q16 > 300000),AND(G16="I",Q16 > 300000),AND(G16="V",Q16 > 1000000),AND(G16="H",Q16 > 600000)),"Y","")
Hope this is what you need.
Formula is= if(and(T3>3,U3>=9,v3<2,w3="NO",X<25),"eligible","ineligible")
If result come ineligible then I want to know that due to which column this result came.
Please guide
Regards
I want the reason for result of IF formula. That if result came was PASS then why? Is it due to column 2 or 3.
Hello SHRIKRISHNA!
To control how the formula is executed, you can use the Evaluate Formula tool. It is located in the menu on the Formula tab.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thanks sir, evaluate formula shows it but I want that this evaluation in column next to result of formula.
Kindly help me to get percentage in multiple amount different percentage ratio
For example:
60000 to 100000 = 5.5%
100001 to 150000 = 8%
150001 to 200000 = 12%
200001 and above 15%
How to calculate if i have many column in different amount and need to get percentage as per above how to use formula, kindly help urgently. Appreciate any can help me in this regards urgently.
Thank you
Pravin Rupapara
Hello Pravin!
You may use the FREQUENCY function to calculate the number of values in the particular range:
=FREQUENCY(B2:B100,F2:F5)
Where B2:B100 - your data range and F2:F5 - the cells that contain your lower bounds (i.e. 60000, 100000, 150000, 200000)
Select the range of empty cells (G2:G6, for instance) that has one cell more than F2:F5. Paste =FREQUENCY(B2:B100,F2:F5) in the formula bar and apply it as an array function by pressing Ctrl+Shift+Enter. Then divide every resulting numbers by the total of values (the formula for the last one would be =COUNTA(B2:B100)
Enter these formulas into H2:H6 and you'll get the percentage you need.
Ok so I'm trying to compile a file that shows if a particular person has been called during the week.
Each Day has a sheet that populates a persons detail from a unique identifier (Tech Id) and can be marked as YES or NO as to whether they have been called that day.
Tech ID, Name, Called?
I then have a weekly review sheet that i need to populate. same column lay out
So if the Tech ID in A2=FS999 and the corresponding tech ID = YES on any of the daily sheets then it should fill in on the weekly tracker. I Just cant for the life of me get my head around the complexity of so many IF's and OR's .
Can Anyone Advise?
Hello Mike!
If I understand your task correctly, the following formula should work for you:
1. To calculate data for several conditions on several sheets, you can use the formula
=SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"),"FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes"))
2. Use this formula in your condition
=IF(SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"), "FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes")) > 0,"Yes","No")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi All,
I need an excel formula to TEXTJOIN of multipel cells with a matching condition of two column vaues. For example.
Column 1 has Managername Manager1, Manager2, Manager3, Manager4, Manager5
Column 2 has Employees EMP1, EMP2, EMP3, EMP4,EMP5, EMP6 etc
column 3 has OnBench, InProject,InProject,InProject,OnBench
I need to list this into other sheet of the same workbook, with the below condition.
List of all employees, with join text separated by comma into single cell against that Manager name who are OnBench.
Can anybody help with this?
Thanks a lot in advance.
Hello Surya!
I recommend using function Vlookup for multiple values/ Read more here.
I hope this will help, otherwise please do not hesitate to contact me anytime.
I need a formula that looks at a date range on 1 sheet but returns the value on a different sheet, looking at values on the original sheet but only IF a different column on the original sheet shows certain TEXT.
I'm doing the formula on a sheet named 'April!'
Data is on 'Ian!' sheet
So column B6:B500 on Ian! will have the date
Column J6:J500 has the income but it is dependant on the value in column C whether it shows as 'New' or 'Renewal'
Please help, thank you
Hello Rachel!
I’m sorry but your task is not entirely clear to me. I need more details to help you. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Write down what specific criteria must be met so that the value of sheet 2 is written to sheet1. Thank you.
I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!
=SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))
If column A is the equally weighted value of a score and column B is the score (0-5 or N/A), and a score of N/A is entered on a row, I want the weight to be removed from the row and equally redistributed across all scored rows, thereby increasing the weights in column A.
Is this possible?
Said another way, if all rows had a numeric score, then all weights would be 3%, for example. However, if there were several rows that got an N/A, for each N/A, the 3% value of the weight would be equally redistributed across column A to show what the final, equally weighted values would be.
Thanks for your help! I hope this question makes sense!
Hello Lisa!
If N/A is a text, not an error value #N/A, then use the following formula to calculate the weight:
= IFERROR (B1/SUM($B$1:$B$50), 0)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Please help me correct these formulas
=(F32); IF(F16:F21="0",) - H47 = F32 but if cells F16:F21 = 0 then H47 Should = 0%
Also I require a formular If C11="good",15; IF C11="Satisfactory",9; IF C11="Poor",0; IF C11="N/A",N/A)
Thank You
Hello Mona!
I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. What you wrote is not a formula.
This formula may be suitable for you, but your explanations are very inaccurate.
=IF(C11="good",15, IF(C11="Satisfactory",9, IF(C11="Poor",0, IF(C11="N/A",#N/A, "" ) ) ) )
Hi
Yes, I figured i messed that up really bad and managed to correct it to this
=IF(C11="Good";15;IF(C11="Satisfactory";9;IF(C11="Poor";0;"N/A"))) which works well,
the only problem is i dont know how to go about having a formula that ignores a N/A and still work out at 100%. I tried yours now and this just leaves the score as a positive.
Hello Mona!
Excel uses the IFERROR function to handle errors. Read more here https://www.ablebits.com/office-addins-blog/excel-iferror-function-formula-examples/
I am looking for help. My worksheet tracks work as well as issues. I need a formula for conditional formatting that will allow for a visual quick identification. This is what I think the formula should look like but I get an error. =IF(G22="Y","CORROSION",""(AND(H22="X","CLEANED"))); or =IF(G22="Y","CORROSION","",IF(G22="Y"(AND(H22="X"),"CLEANED"))
The first part of the Formula works it is trying to add the second half that causes the error. Anyone have a suggestion?
Hello Jesse!
Your formula contains errors. 1. You cannot use the = sign inside a formula. 2. The conditions AND and OR are incorrectly described. You have not explained how the formula should work, so I can’t fix it. To do it yourself, read the instructions in this article above.
I hope this will help, otherwise please do not hesitate to contact me anytime.
On my spread sheet I have criteria that represent if an item is corroded or not, block 1 "item", block 2 is "inspected", block 3 "corroded", block 4 'cleaned/primed", and block 5 "notes". If it is corroded I put a "Y" in the corroded block. In my note block I have a formula, =IF(G4="Y","CORROSION",""), if this block has a "Y" it will show the word "CORROSION" this works perfectly. What I need to do is add to the formula that considers the corroded block with a "Y" and the clean primer block with a "X" to return with the word "REPAIRED". Is there a way to do this? I need this as a quick reference so at a visual glance the item can be easily identified as repaired.
Hello Jesse!
If I understand your task correctly, the following formula should work for you:
=IF(G4="Y", IF(H4="X","Repaired","CORROSION"),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you sir works perfectly.
I am trying to figure out how to return one value in a cell by evaluating 6 other cells. For instance, if cell C2 has a valid value, return C2. If "NA" I need it to look at D2 and do the same evaluation and return the valid value if not "NA". So, column c2 = CAR1, columns d2 through h2 have NA. I want column B2 to say CAR1. If column d2 = CAR2 and column c2, e2 through h2 have NA. I want column b2 to say CAR2. I am guessing I need a string but cannot figure it out. Thanks!
Hello Frank!
If I understand your task correctly, the following formula should work for you:
=INDEX(C1:H1,1,MATCH("NA",C1:H1,0)-1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
=IF(O23="Inactive",0,IF(OR(L23=$L$3,L23=$L$4)=TRUE,$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))
The last if functions works when on its own, but when added to the previous functions it does not work. I do not see where the issue is: only adding one extra agruement to the formula to say if additional something is "Yes" then please add 150 to the amount.
Hello!
I'm sorry, I don't quite understand your case based on the details you provided. As for your formula, it should look like the one below:
=IF(O23=”Inactive”,0,IF(OR(L23=$L$3,L23=$L$4),$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))
If the formula doesn't work the way you'd like it to, please give me some more details about its conditions, their priority and specify what result you need to get. Maybe although the formula you created works fine, it doesn't match these criteria. If you give me more information about your task and the expected result, I'll be able to find the right solution for you. Thank you.
Hi I need a formula to state this
In there will be Yes or No. If its Yes then use cell A1 if its No then use cell A2
Hello,
Please try the following formula:
=IF([condition],A1,A2)
You can learn more about Excel IF statement in this article on our blog.
Hope this is what you need.
Sir i want if ((1 to 2 = 2 , 2 to 4 = 3, 5 to 9 = 5 ))how to make that on if logic in single cell if any argument Parameter in between 1 to 2 must showing 2 , argument Parameter in between 3 to 5 = 3
Hello!
If I understand your task correctly, please try the following formula:
=IF(AND(A1 >= 1,A1<= 2),2,IF(AND(A1 > 2,A1 <= 4),3,IF(AND(A1 >= 5,A1 <= 9),5,"")))
I hope it’ll be helpful.
Hello, I need your help. I have a formula that is trying to figure out over and under numbers to meet goals.
=IF($B$4<$C$1,"")
actual B4=20 and C1=15(goal)
If the number on B4 is less than 15, I have it giving us a blank, which works fine. But I cant figure out how to add another IF formula that will tell it to give me the number we are over our goal by. In this case, total is 20, goal is 15, answer should be 5. So how do I add an if to my formula?
=IF($B$415, XXX)
Hello Maria!
If I understand your task correctly, please try the following formula:
=IF($B$4 < 15,"",$B$4-$C$1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
WOW! Thank you. Here I thought I needed a second IF statement. Thanks so much for your help. Maria.
I am very new to excel but I have a very detailed comparison I do manually on an excel sheet currently and I think you maybe able to help me.
I need to compare three columns and depending the message in those columns I need an out come.
Data:
A1 will contain update needed or blank
B2 will contain coordinator needed or blank
C2 will contain licensed or blank
Anytime column C2 equals Not Licensed no matter what any of the other columns say then I need column D2 to say Ineligible
If C2 is blank and b2 is coordinator needed and A1 is update needed the d2 needs to say coordinator and update needed
If c2 is blank and b2 is blank but A1 is update needed then I need D2 to say Eligible update
If all are blank then I need D2 to say No Action Needed
Thanks for any help !
Hello Diane!
If I understand your task correctly, please try the following formula:
=IF(C2="Not Licensed","Ineligible", IF(AND(C2="",B2="coordinator needed",A1="update needed"),"coordinator and update needed", IF(AND(C2="",B2="",A1="update needed"),"Eligible update", IF(AND(C2="",B2="",A1=""),"No Action Needed", "" ) ) ))
Hope this is what you need.