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 52. Total comments: 4557
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.
In same sheet read the 3 column V to x and match the condition of 25> cases in AC column and return the gift he qualified in AE column.
hi,
my requirement is, I have 3 columns. If column 1 value meets my requirement and the columen 2 text is "x", extract column 2 text. Else no. could you please help how I can write formula?
Hello!
You can learn more about about functions IF and AND in Excel in this article on our blog.
Hope you’ll find this information helpful.
Hi
I am using a history database and would like to make it a little more efficient. Its purpose is to code surnames using four characters but when there are fewer than four characters, to fill in any gaps with a full stop; for example KIM would become KIM. If I have a column of surnames, is there a formula that would allow me to identify those with fewer than four code letters and which would add the full stop for me. The current process requires me to check each entry and for a large database it takes an age.
Hello Pail!
If I understand your task correctly, please try the following formula:
=IF(LEN(TRIM(A1)) < 4,"less than 4",A1)
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.
if AH1=Conus and D1=12, then 17
if AH1=Conus and D1<12, then 15
if AH1=Oconus, then 28
Hello Jessica!
If I understand your task correctly, please try the following formula:
=IF(AH1="Conus",IF(D1=12,17,IF(D1 < 12,15,"")),IF(AH1="Oconus",28,""))
ok, i need to throw another variable in - can this be done?
if AH1=Conus and D1=12, AND G1=9820580 or 159384 ....then 17
if AH1=Conus and D1=12, AND G1=6620363 ....then 15
if AH1=Conus and D1<12, then 15
if AH1=Oconus, then 28
Hello Jessica!
Please try the following 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,""))
I hope it’ll be helpful.
WOW. thank you so much
Thank you so much!
Thank you sir for your attention, if B1"", C1"", & D1"", it should give me the value of both K1, L1 & M1 & so on.
"Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of M1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
Thanks.
Hello Enity!
Unfortunately, you did not give me detailed explanations. What formula did you use? Are you checking the condition in 15 cells at the same time? In which cell do you want to write the result? What should this result look like? How is the sum of the values of K1, L1 & M1 and so on? What are 15 arguments if there are only 10 columns between columns B and K? I can assume the following formula:
=IF(SUMPRODUCT(--(B1=""),--(C1=""), --(D1=""),--(E1=""),--(F1=""),--(G1=""), --(H1=""),--(I1=""),--(J1=""))=1, K1,B1)
But this is just a guess. And I'm not a telepath.
I have two columns of data with the number set at 2 decimal places. column A for example in Cell A2 could read '10.2' and Cell B2 could read '11.3'. I want to be able to have cell C3 to state 'out of tolerance' if the range is outside of 10%. Is that possible?
Hello Shane!
You did not explain what it means "the range is outside of 10%". Maybe the following formula should work for you:
=IF((B2-A2)/A2>0.1,"out of tolerance","ok")
Expression (B2-A2)/A2>0.1 can be replaced with another.
I hope it’ll be helpful.
Hi all,
How to get different statement in one cell based on the different date ,
For example:
I have 5 document received date and 6 document sent date.
from that once document sent from my side i need statement automatically "Sent"
from my side document is pending statement should come " Pending"
and if i entered any date in completed column statement should come " completed"
Hello Jaybal!
I’m sorry but your task is not entirely clear to me. Excel cannot automatically indicate the status of a document. How does he know that you sent the document? It’ll help me understand it better and find a solution for you. Thank you.
I will receive document from vendor for review and once documents are okay i will approve that doc, if document rejected they will resubmit.
In my tracker Approved mean i will enter code "A"
if document reject i will enter code "C"...
These sequence will happen more than 7 to 8 times.
My requirement is Once i entered in C / A code i need result Approved /rejected in any one cell
Exampple:
(I need status automatically)
First time doc received A2= C Status "Rejected"
2nd time doc received C2= C Status "rejected"
3rd time doc received E2=A Status " Approved"
like this i have to check based on the code
Hello Jayabal!
If I understand your task correctly, the following formula should work for you:
=IF(COUNTIF(A2:E2,"A")>0,"Approved ","Rejected")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi I need to do a calculation total. I have a column called Quote/Won with one word in each row. I have a Labour £ column. I need to create a total of all labour revenue won. So if any figure in work labour column is won, the revenue figure shows in the total field. (single combined total field)
eg.
Total Labour revenue won £ 200
Won/Quoted Column---- Work Labour £ Column
Won £100
Quoted £150
Won £100
Hello Deb!
If I understand your task correctly, the following formula should work for you:
=SUMIF(A2:A7,"won",B2:B7)
You can learn more about SUMIF in Excel in this article on our blog.
Hope you’ll find this information helpful.
Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of L1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
Thanks.
I have this formula =IF((AND($F2>=VLOOKUP($C2&""&F$1,Sheet3!$C$5:$E$1200,2,FALSE),$F2=VLOOKUP($C2&""&G$1,Sheet3!$C$5:$E$1200,2,FALSE),$G2=VLOOKUP($C2&""&H$1,Sheet3!$C$5:$E$1200,2,FALSE),$H2=VLOOKUP($C2&""&I$1,Sheet3!$C$5:$E$1200,2,FALSE),$I2=VLOOKUP($C2&""&J$1,Sheet3!$C$5:$E$1200,2,FALSE),$J2=VLOOKUP($C2&""&K$1,Sheet3!$C$5:$E$1200,2,FALSE),$K2=VLOOKUP($C2&""&L$1,Sheet3!$C$5:$E$1200,2,FALSE),$L2=VLOOKUP($C2&""&M$1,Sheet3!$C$5:$E$1200,2,FALSE),$M2=VLOOKUP($C2&""&N$1,Sheet3!$C$5:$E$1200,2,FALSE),$N2=VLOOKUP($C2&""&O$1,Sheet3!$C$5:$E$1200,2,FALSE),$O2=VLOOKUP($C2&""&P$1,Sheet3!$C$5:$E$1200,2,FALSE),$P2=VLOOKUP($C2&""&Q$1,Sheet3!$C$5:$E$1200,2,FALSE),$Q2=VLOOKUP($C2&""&R$1,Sheet3!$C$5:$E$1200,2,FALSE),$R2=VLOOKUP($C2&""&S$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2>=VLOOKUP($C2&""&T$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2=VLOOKUP($C2&""&U$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2>=VLOOKUP($C2&""&V$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2=VLOOKUP($C2&""&W$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2>=VLOOKUP($C2&""&X$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,2,FALSE),$Z2<=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,3,FALSE))), "", 1)
It is working normally but it is very long, Is their any way to make it easier.
My apologies if you answered this already. I have a spreadsheet with Monday-Sunday in each column (A-G). State Names in Columns H-O. Unique People names in P column and their phone number in column Q.
Monday
Monday
Monday
Monday
Monday
Monday
I want to know who is available to travel on Monday to Utah.
I want the formula to provide me with a list of people and their phone number
Hello Sylvia!
I think that in your case the simplest and the most convenient way is to make use of Advanced Filter in your table instead of formulas. Please find advice on applying filters on our blog:
how to add filter, about advanced filter, highlight duplicates
Set a filter in the table and indicate criteria (what values you would like to see) in the necessary columns.
how to connect two condition under each have some conditions?
Hello!
See above Example 1. Using IF & AND function in Excel
I am trying to Populate a Date cell in L7 and I have two cells to choose from T7 and U7. I want L7 to be T7 if T7 has a date if not I want it to be U7 since that will always have a date. The Date in T7 will change due to plan optimization.
Hello Blaise!
If I understand your task correctly, maybe the following formula should work for you:
=IF(T7<>"",T7,U7)
If there is anything else I can help you with, please let me know.
I am trying to get a formula as per condition below:
Condition 1 - 0<A6<=2
Condition 2 - 0<B6<=2
Condition 3 - N/A
"OK" if criteria as below met.
A6 B6
1 1
1 2
2 1
2 2
N/A 1
1 N/A
N/A N/A
"Not OK" if criteria as below met.
A6 B6
1 3
2 3
3 1
3 2
3 3
N/A 3
3 N/A
Need help. Thanks
Hi, I am looking for a formula to build where if the answer in cell E10 = Yes, then populate information in cell D10. As an example, if Yes, then it would populate YM-SL. The information in column D is various sizes. Or I could create a formula in column E, that if Yes, populate with answer from D. Either formula would be helpful. Thank you
Hello Robin!
I’m sorry but your task is not entirely clear to me. Do you want to fill in cell E or D? Maybe this is a formula =IF(E10="Yes",D10,"")? Could you please describe it in more detail? Thank you!
hello, iam trying to counts pages based on if formaula, but i am not getting the answerer
If pages are 1 to 5 my answer should be 1
if more than 5 pages and less than or equal to 14 should be 2
If anything more than 14 pages my answer should be 3 - but this i am not getting
Here is my formula which i applied, can anyone suggest me on this
=IF(B25,"2",IF(B215,"3")))
Hello Ratheesha!
Please try the following formula:
=IF(B2>=1,(IF(B2<=5,1, (IF(B2<=14,2,3)))),0)
Hope you’ll find this information helpful.
I am trying to create a formula that will calculate the LTV (loan to value) I can use. Here is what I have so far.
=IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%")))
However, I want to add another two IF conditions that will increase each percentage by 5% if they have 740+ credit AND $120000 annual salary. Thanks in advance for the assistance!
Hello Yixo!
If I understand your task correctly, maybe the following formula should work for you:
=IF(AND(D4>=740000,D5>=120000), IF(D3>=850000, "80%", IF(D3>=500001, "85%", IF(D3<=500000, "90%"))),IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%"))))
Hope you’ll find this information helpful.
Hi,
Would the if function work for this?
Port/City Vancouver Montreal
Shanghai 27 34
Shenzhen 28 35
Guangzhou 35 42
Example:
Select: Shanghai, Vancouver
Time: 27 days
Select: Shanghai, Montreal
Time 34 days
Hello Jesse!
Please try the following formula:
=INDEX(B2:C4, MATCH("Shanghai",A2:A4,0), MATCH("Vancouver",B1:C1,0))
or
=INDEX(B2:C4, MATCH($E$2,A2:A4,0), MATCH($E$1,B1:C1,0))
I hope this will help, otherwise please do not hesitate to contact me anytime.
I want to put this equation in cell H57
1) if cell I54 value is = or than 20 and than 40, then multiply (I54 X N44)
so please advise how to right this equation
Hi there,
I have a total of "overtime and regular hours". I want my regular to show the hours in total cell of OV and RE, but not exceed over 40.
for example:
Total of overtime and regular is 32.
Regular cell show the 32.
but when the overtime and regular cell is > 40, the regular cell show only 40.
Can you please help me with a formula?
Hello Aimal!
If I understand your task correctly, the following formula should work for you:
in B21:
=IF(SUM(A1:A20,B1:B3)>40, 40, SUM(B1:B20))
where A1: A20 is overtime, B1: B20 is a regular time.
If that's not what you wanted, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
1)If= first month(1 Jan to 31 Jan) all floors commission 2%
2)If= second month (1feb to 29 Feb) floor wise commission
Lower floor - 3%
Middle floor - 3.5%
Higher floor - 4 %
3) if = third month ( 1 March onwards) onwards floor wise commission
Lower floor - 2 %
Middle floor - 2.5%
Higher floor -3%
Please help how to create formula in 1 cell in excel
Hello Swapnil!
if you write data -
A B C D E
Month 1 2 3 2/10/2020
Lower floor 2 3 2 Middle floor
Middle floor 2 3.5 2.5
Higher floor 2 4 3
Please try the following formula:
=INDEX(B2:D4,MATCH($E$2,A2:A4,0), MONTH(E1))
I hope this will help, otherwise please do not hesitate to contact me anytime.
sorry I do not understand of this
A1 - Month, A2 - Lower floor, A3 - Middle floor, A4 - Higher floor, B1 - 1, B2 - 2, B3 -2, B4 - 2, C1 - 2, C2 - 3, C3 - 3.5, C4 -4 and so on. E1 - 2/10/2020 , E2 - Middle floor
please help its very urgent
Hello!
If you want to learn something, try to understand how the formula works, and not ask for help all the time.
=IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(2021,3,31),3,0))))), "Date out of range")
If you write table
Column a : name of customer
Column b : source
Column c : source name
Column d : value
Column e : commission %(2%)
Column f : need formula this column
Source type 3
* broker
* Ref
* direct sale
Commission type
1)If broker sale 1 product get 2% commission ( value * 2%)
2 ) if any existing sale his ref through 1 product get amount benefit
* sale 1nd product deal get 10000 rs
*sale 2nd prduct deal get 20000 rs
* sale 3 rd product deal get 30000rs
And
3) if direct sale get 0% commission
Please help me how to merge in one cell (f column) formula
Hello!
Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
Thank you very much it's working
but one more problem one Month Condition was changed. Condition is Date of period so formula not working...
*Date of period
1 ) 1 Jan 2019 to 2 Feb 2020 -commission all floors 2%
2 ) 3 Feb 2020 to 29 Feb 2020 - commission floor wise( L, M, H) 3 %, 3.5%,4%
3 ) 01 March 2020 to 31 march 2021* commission floor wise (L, M, H) 2%,2.5%,3%
Can you please help .....
Hello Swapnil!
Please try the following formula:
=IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(YEAR(TODAY()),3,31),3,0))))), "Date out of range")
Hope you’ll find this information helpful.
Please can someone help me with an excel formula?
3 sources of get commision
1)Comany
2) Customer ref
3) Direct sale
1) If multiple company commission for 2 %
2)if customer ref commision
*first ref commission rs 10k
*Second ref commission 20k
And
*Third ref commission 30 k
3) Direct commission "0%"
How to get commission formula in one cell
Hello!
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 specify what you were trying to find. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
And one more condition add
3 types of sources in this
X, y, z
X and z eligible for all condition commission
But y not eligible for all. Only eligible 2% commission in all types of condition
Please help....
Formula not working on pending period 01 april 2020 to 31-03-2021 ...working only march 2020 ..
Third condition not completed.. Please help.
=If(and(A2="A",B2="A" or "B"),"Yes","No")
Using like this in excel, if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify
Hello
Please try the following formula:
=IF(A2="A",IF(OR(B2="A",B2="B"), "Yes","No"),"No")
Hope you’ll find this information helpful.
=If(and(A2="A",B2="A" or "B"),"Yes","No")
Using like this if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify
I need a formula that says if cell "M" is "true" and Cell "N" is "yes" then sum cells Q & T but if cell "M" is "false" and "N" is "no" the cell T but if cell "m" is "false" and cell "n" is "yes" cell T
I have been working on a formula for an movie list of 1500 Dvds, Blu-rays, and 4ks that I'm backing up on 3 HDDs, 1 for each type. Now all the movies have a "type" column C2 which will only be a value "SD", "SD/HD", "HD", "HD/UHD", "UHD". A "on HDD" column I2 which will only be a value "Y", "N", "Y/Y", "Y/N", "N/Y", "N/N". These represent if the movie was copied to hard drive/s (some have read errors), I have 3 more columns 1 for each drive L2 = drive 1 SD, M2 = drive 2 HD, N2 = drive 3 UHD. Now if I have a DVD/Blu-ray movie (SD/HD) the Y/Y part matches the type so if the DVD SD did not make it to hard drive but the blu-ray did it would be "N/Y" my ? Is if I just want a "X" in columns L,M,N if the movie made it to the coralating HDD =IF(AND(OR(C2="SD",AND(I2 ="Y",OR(C2="SD/HD",AND(I2="Y/Y",OR(I2="Y/N", "X","") I know I'm on the right track, I will do that for each drive column changing out "Type" but I need help with the correct way to write out the formula, Please Help, Thanks.
Hello Ryan!
If I understand your task correctly, the following formula should work for you:
=IF(OR(
AND(C2="SD",I2 ="Y"),
AND(
C2="SD/HD",OR(
I2="Y/Y",I2="Y/N",I2="X",I2="")
)
),TRUE,FALSE)
For me to be able to help you better, please describe your task in more detail. Your explanation of the formula is very difficult to understand. It’ll help me understand it better and find a solution for you. Thank you.
=IF((B5+C5)<=25400,"900"(B5+C5)<=29900,"1100"(B5+C5)<=30400,"1300"(B5+C5)<=33400,"1500"(B5+C5)<=35400,"1700",IF((B5+C5)<=37300,"1800",IF((B5+C5)<=41100,"2300",IF((B5+C5)<=44500,"2600",IF((B5+C5)<=50200,"2900",IF((B5+C5)<=51600,"3100",3200))))))
how can use these conditions in a formula excel doesnot allow morethan 8
if 400 kg = 17.5
If 2000KG=22
Based on above assumption how to calculate the unit price for 375KG
what is the best formula for that ?
I would like to know how to determine the effect of pest infestation on crop using the if statement. I am having trouble writing a formula. Can someone help me please.
Hello Michael!
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.
Hello and thank you ahead of time! Been working on finding a code for my spreadsheet for quite some time now, so I am very grateful for this site! Please help!
I have two sheets in a workbook. Sheet 1 contains data to be retrieved into Sheet 2.
Sheet 2!A1 is the "ID Number" to look up in Sheet 1 Column A, and Match or Lookup? if Sheet 1 Column D is "Yes", then return "Purple", if Column E is "Yes" then return "Red",if Column F is "Yes" then return "Blue",if Column G is "Yes" then return "Black"
The code I am working with and return value in:
Sheet 2 Cell D4
VLOOKUP(A1,Sheet 1!A:A,IF(Sheet 1!D:D="Yes","Purple),IF(Sheet 1!E:E="Yes","Red),"IF(Sheet 1!F:F="Yes","Blue),IF(Sheet 1!G:G="Black")))
Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black
Hope I was able to explain what I am trying to achieve
=IF($F$24,C6*0.75,IF($F$2>=8,C6*0)))
hoping someone can help
im trying to sort my lab charges
if i have in column c I have "cbc" i want column e to have price for cbc which is $8.98
if in column c i have "bmp" then column e should show $14.56
etc
i have about 60 different labs and pricing. what is easiest way to do this so i dont have to manually enter the price each time
thanks!
Hello Aracely,
Please try a VLOOKUP formula. You can learn more about VLOOKUP in Excel in the above linked article on our blog.
Hope you’ll find this information helpful.
I have a worksheet with over 10K lines of data which requires multi IF statements based on different criteria. I can't seem to write or find and IF statement that computes in a range. Example. If the value is above x and below y, multiply by Z. Can you help?
Thank you
Hello !
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.
I have a spreadsheet with rows of data results for 5 tests that have been done and as such if the test has been done would expect to see either a PASS or a FAIL in each cell. However if one of test in that row is not done then that particular cell would be blank. Now I want to add a column which looks at each entry in the row and if any fails then it will remind the user to add some additional information, have tried using logic but I cannot seem to get it to ignore the cell if it is blank. Thus I am after something to solve the following
PASS, PASS, PASS, PASS, PASS = No Action required
PASS, " ", PASS, PASS, PASS = No Action required i.e ignore any blanks
PASS, PASS, FAIL, PASS, PASS = Add addition Info as it sees a FAIL
PASS, " ", FAIL, PASS, PASS = Add addition Info, again as it sees a FAIL
Anyway any help would be greatly appreciated as this is doing my head in...
Hello Andrew!
If I understand your task correctly, the following formula should work for you:
=IF(SUM(--(A1:E1="PASS"),--(A1:E1=""))=5, "No Action", "Action")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alexander,
Firstly thanks for the response, much appreciated. Now apologies, my fault as I should have probably mentioned that there were other cells between each of the entries which contains other information as such I need to look at each of these cells individually and then make a collective decision on the action, thus the spreadsheet breaks down as follows:
Cell 1 = Numeric data, Cell 2 = Time(hh:mm:ss), Cell 3 = Text, Cell 4 = Text & Cell 5 = Result (PASS/FAIL/"")
This then repeats itself another 4 times as each row has 5 probable tests, thus all I want to do is look at Cell 5 of each test and from those entries ascertain whether there is an action to be carried out for that row. i.e. if they are all PASS or blank then no Action is necessary, however if a FAIL is seen then ACTION is required.
Hope this makes sense and apologies for messing you about as I know you guys are likely busy.
Hello Andy!
Change the formula
=IF(SUM(--(E1="PASS"),--(E1=""), --(J1="PASS"),--(J1=""),--(O1="PASS"),--(O1=""), --(T1="PASS"),--(T1=""), --(Y1="PASS"),--(Y1=""))=5, "NO Action", "Action")
I hope it’ll be helpful.
I used to make a formulae using the following scenarios;
> 1,000 amount would appear in the column to all who are non regular status
> 500 for non regular
example: If employee is on a non regular status the equal amount due for employee we will are 500.
Hello Amay!
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.
Hi. Please help
I have 23,000 in income. I need to pay 0% in the first 2,000.
3% up to 5,000.
4% from 5,ooo to 10,000
5% on anything above 10,000
I need all the calculation in one formula.
Hello Marina!
Hello
If I understand your task correctly, the following formula should work for you:
=((A1-10000)*0.05)*(A1>10000) + ((A1-5000)*0.04)*(A1>5000)*(A1<10000) + (5000*0.04)*(A1>=10000) + (A1*0.03)*(A1<5000) + (5000*0.03)*(A1>=5000)
I hope this will help, otherwise please do not hesitate to contact me anytime.
In other words, we don't pay anything in the first 2,000 and we pay 3% from 2k up to 5K.
Thank you so much again
Hello Marina!
Please use the following formula
=((A1-10000)*0.05)*(A1>10000)+((A1-5000)*0.04)*(A1>5000)*(A1<10000)+(5000*0.04)*(A1>=10000)+((A1-2000)*0.03)*(A1<5000)*(A1>2000)+(3000*0.03)*(A1>=5000)
Hi Alexander. You are awesome. Thanksfor the help. I review and everything looks good except the last part of the formula. That should be 0% anything 2,000 or less and from 2,000 to 5000 is 3%. We are supper close.
650 ((A1-10000)*0.05)*(A1>10000) correct
200 ((A1-5000)*0.04)*(A1>5000)*(A1=10000) correct
150 (A1*0.03)*(A1=5000) review
Hello Hino!
If cells G15 and G9 contains numbers, it is not quite clear why you get an error. I can suppose that there is a number in one of the cells and the other one contains a space or another non-printing character. Anyway, I recommend using the IFERROR function to process errors in formulas. You can find more info about this function here: https://www.ablebits.com/office-addins-blog/excel-iferror-function-formula-examples/
Alternatively, you may find our Remove Characters tool useful for searching and deleting spaces and other invisible symbols. The tool is available as a part of our Ultimate Suite for Excel. Feel free to install the add-in in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
I hope it’ll be helpful.
Hi Svetlana
I want to add two cells (G15+G9) and then use the result in another formula. However if one of the cells is empty I get a #value result. How can I get the result of either one of the cells is empty.
what formula should I write here?
90-94, with honors
95-97, with high honors
98-100, with highest honors
Please help.
Hello!
Please use the following formula
=IF(A1 >= 90,IF(A1 >= 94,"Honor",IF(A1 >= 97, "High Honor", "Highest Honor")),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
I have two products in kilograms with different Rates and if I sell my products more than first sell . I want continually minus .like
Qty Rate Amount Qty Rate Amount Sell qty sell rate Sell amount
12*15=180,. 10*17=170. Formula cell 15*....=....
Thanks for your providing this is good lesson for me.