Excel IF statement with multiple AND/OR conditions, nested IF formulas, and more
In Part 1 of our Excel IF function tutorial, we started to learn the nuts and bolts of the Excel IF function. As you remember, we discussed a few IF formulas for numbers, dates and text values as well as how to write an IF statement for blank and non-blank cells.
However, for powerful data analysis, you may often need to evaluate multiple conditions at a time, meaning you have to construct more sophisticated logical tests using multiple IF functions in one formula. The formula examples that follow below will show you how to do this correctly. You will also learn how to use Excel IF in array formulas and learn the basics of the IFEFFOR and IFNA functions.
How to use Excel IF function with multiple conditions
In summary, there can be 2 basic types of multiple conditions - with AND and OR logic. Consequently, your IF function should embed an AND or OR function in the logical test, respectively.
- AND function. If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met; otherwise it returns FALSE.
- OR function. In case you use the OR function in the logical test, Excel returns TRUE if any of the conditions is met; FALSE otherwise.
To better illustrate the point, let's have a look at a few IF examples with multiple conditions.
Example 1. Using IF & AND function in Excel
Suppose, you have a table with the results of two exam scores. The first score, stored in column C, must be equal to or greater than 20. The second score, listed in column D, must be equal to or exceed 30. Only when both of the above conditions are met, a student passes the final exam.
The easiest way to make a proper formula is to write down the condition first, and then incorporate it in the logical_test argument of your IF function:
Condition: AND(B2>=20, C2>=30)
IF/AND formula:
=IF((AND(C2>=20, D2>=30)), "Pass", "Fail")
Easy, isn't it? The formula tells Excel to return "Pass" if a value in column C >=20 AND a value in column D >=30. Otherwise, the formula returns "Fail". The screenshot below proves that our Excel IF /AND function is correct:
In practice, a seemingly correct IF / AND formula may result in an error because of this specificity. For example, the below formula will return "Divide by Zero Error" (#DIV/0!) if cell A2 is equal to 0:
=IF(AND(A2<>0,(1/A2)>0.5),"Good", "Bad")
The avoid this, you should use a nested IF function:
=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")
Example 2. Using IF with OR function in Excel
You use the combination of IF & OR functions in a similar way. The difference from the IF / AND formula discussed above is that Excel returns TRUE if at least one of the specified conditions is met.
So, if we modify the above formula in the following way:
=IF((OR(C2>=20, D2>=30)), "Pass", "Fail")
Column E will have the "Pass" mark if either the first score is equal to or greater than 20 OR the second score is equal to or greater than 30.
As you see in the screenshot below, our students have a better chance to pass the final exam with such conditions (Scott being particularly unlucky failing by just 1 point : )
More formula examples can be found in Excel IF OR functon.
Example 3. Using IF with AND & OR functions
In case you have to evaluate your data based on several sets of multiple conditions, you will have to employ both AND & OR functions at a time.
In the above table, suppose you have the following criteria to evaluate the students' success:
- Condition 1: column C>=20 and column D>=25
- Condition 2: column C>=15 and column D>=20
If either of the above conditions is met, the final exam is deemed passed, otherwise - failed.
The formula might seem tricky, but in a moment, you will see that it is not! You just have to express two conditions as AND statements and enclose them in the OR function since you do not require both conditions to be met, either will suffice:
OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)
Finally, use the above OR function as the logical test in the IF function and supply value_if_true and value_if_false arguments. As the result, you will get the following IF formula with multiple AND / OR conditions:
=IF(OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)), "Pass", "Fail")
The screenshot below indicates that we've got the formula right:
Naturally, you are not limited to using only two AND/OR functions in your Excel IF formulas. You can use as many logical functions as your business logic requires, provided that:
- In Excel 2016, 2013, 2010 and 2007, your formula includes no more than 255 arguments, and the total length of the formula does not exceed 8,192 characters.
- In Excel 2003 and lower, you can use up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.
Using multiple IF statements in Excel (nested IF functions)
If you need to create more elaborate logical tests for your data, you can include additional IF statements in the value_if_true and value_if_false arguments of your Excel IF formulas. These multiple IF functions are called nested IF functions and they may prove particularly useful if you want your formula to return 3 or more different results.
Here's a typical example: suppose you want not simply to qualify the students' results as Pass/Fail, but define the total score as "Good", "Satisfactory" and "Poor". For instance:
- Good: 60 or more (>=60)
- Satisfactory: between 40 and 60 (>40 and <60)
- Poor: 40 or less (<=40)
To begin with, you can add an additional column (E) with the following formula that sums numbers in columns C and D:
=C2+D2
And now, let's write a nested IF function based on the above conditions. It's considered a good practice to start with the most important condition and make your functions as simple as possible. Our Excel nested IF formula is as follows:
=IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor "))
As you see, just one nested IF function is sufficient in this case. Naturally, you can nest more IF functions if you want to. For example:
=IF(E2>=70, "Excellent", IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor ")))
The above formula adds one more conditions - the total score of 70 points and more is qualified as "Excellent".
For more information about Excel IF with multiple conditions, please see How to use nested IF in Excel.
Using Excel IF in array formulas
Like other Excel functions, IF can be used in array formulas. You may need such a formula if you want to evaluate every element of the array when the IF statement is carried out.
For example, the following array SUM/IF formula demonstrates how you can sum cells in the specified range based on a certain condition rather than add up the actual values:
=SUM(IF(B1:B5<=1,1,2))
The formula assigns a certain number of "points" to each value in column B - if a value is equal to or less than 1, it equates to 1 point; and 2 points are assigned to each value greater than 1. And then, the SUM function adds up the resulting 1's and 2's, as shown in the screenshot below.
Using IF function together with other Excel functions
Earlier in this tutorial, we've discussed a few IF formula examples demonstrating how to use the Excel IF function with logical functions AND and OR. Now, let's see what other Excel functions can be used with IF and what benefits this gives to you.
Example 1. Using IF with SUM, AVERAGE, MIN and MAX functions
When discussing nested IF functions, we wrote the formula that returns different ranking (Excellent, Good, Satisfactory or Poor) based on the total score of each student. As you remember, we added a new column with the formula that calculates the total of scores in columns C and D.
But what if your table has a predefined structure that does not allow any modifications? In this case, instead of adding a helper column, you could add values directly in your If formula, like this:
=IF((C2+D2)>=60, "Good", IF((C2+D2)=>40, "Satisfactory", "Poor "))
Okay, but what if your table contains a lot of individual scores, say 5 different columns or more? Summing so many figures directly in the IF formula would make it enormously big. An alternative is embedding the SUM function in the IF's logical test, like this:
=IF(SUM(C2:F2)>=120, "Good", IF(SUM(C2:F2)>=90, "Satisfactory", "Poor "))
In a similar fashion, you can use other Excel functions in the logical test of your IF formulas:
IF and AVERAGE:
=IF(AVERAGE(C2:F2)>=30,"Good",IF(AVERAGE(C2:F2)>=25,"Satisfactory","Poor "))
The formulas retunes "Good" if the average score in columns C:F is equal to or greater than 30, "Satisfactory" if the average score is between 29 and 25 inclusive, and "Poor" if less than 25.
IF and MAX/MIN:
To find the highest and lowest scores, you can use the MAX and MIN functions, respectively. Assuming that column F is the total score column, the below formulas work a treat:
MAX: =IF(F2=MAX($F$2:$F$10), "Best result", "")
MIN: =IF(F2=MIN($F$2:$F$10), "Worst result", "")
If you'd rather have both the Min and Max results in the same column, you can nest one of the above functions in the other, for example:
=IF(F2=MAX($F$2:$F$10) ,"Best result", IF(F2=MIN($F$2:$F$10), "Worst result", ""))
In a similar manner, you can use the IF function with your custom worksheet functions. For example, you can use it with the GetCellColor / GetCellFontColor functions to return different results based on a cell color.
In addition, Excel provides a number of special IF functions to analyze and calculate data based on different conditions.
For example, to count the occurrences of a text or numeric value based on a single or multiple conditions, you can use COUNTIF and COUNTIFS, respectively. To find out a sum of values based on the specified condition(s), use the SUMIF or SUMIFS functions. To calculate the average according to certain criteria, use AVERAGEIF or AVERAGEIFS.
For the detailed step-by-step formula examples, check out the following tutorials:
- How to use the COUNTIF in Excel
- Using Excel COUNTIFS and COUNTIF with multiple conditions
- SUMIF in Excel - formula examples to conditionally sum cells
- How to use Excel SUMIFS and SUMIF with multiple criteria
Example 2. IF with ISNUMBER and ISTEXT functions
You already know a way to spot blank and non-blank cells using the ISBLANK function. Microsoft Excel provides analogous functions to identify text and numeric values - ISTEXT and ISNUMBER, respectively.
Here's is example of the nested Excel IF function that returns "Text" if cell B1 contains any text value, "Number" if B1 contains a numeric value, and "Blank" if B1 is empty.
=IF(ISTEXT(B1), "Text", IF(ISNUMBER(B1), "Number", IF(ISBLANK(B1), "Blank", "")))
Example 3. Using the result returned by IF in another Excel function
Sometimes, you can achieve the desired result by embedding the IF statement in some other Excel function, rather than using another function in a logical test.
Here's another way how you can use the CONCATINATE and IF functions together:
=CONCATENATE("You performed ", IF(C1>5,"fantastic!", "well"))
I believe you hardly need any explanation of what the formula does, especially looking at the screenshot below:
IF function vs. IFERROR and IFNA
Both of the functions, IFERROR and IFNA, are used to trap errors in Excel formulas and replace them with another calculation, predefined value or text message. In earlier Excel versions, you can use the IF ISERROR and IF ISNA combinations instead.
The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA specialize solely in #N/A errors.
Here is the simplest example of the IFERROR formula:
=IFERROR(B2/C2, "Sorry, an error has occurred")
As you see in the screenshot above, column D displays the quotient of the division of a value in column B by a value in column C. You can also see two error messages in cells D2 and D5 because everyone knows that you cannot divide a number by zero.
In some cases, however, you may not want to trap all errors, but rather test the condition causing a specific error. For example, to replace a divide by zero error with your own message, use the following IF formula:
=IF(C2=0, "Sorry, an error has occurred", B2/C2)
And that's all I have to say about using the IF function in Excel. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
2,949 responses to "Excel IF statement with multiple AND/OR conditions, nested IF formulas, and more"
=IF(A4="big creek",“Karen”,IF(A4="vista AOR","Jim",IF(A4="alhambra","Martin")))
I know I can't use * for remaining characters with IF function. Is there a way to use multiple IF statements with partial texts and not case sensitive? The above did not work.
Really appreciate the help!
Hello!
Instead of the "* big *" condition, you can use the formula to find the value in the text
ISNUMBER(SEARCH("big",A4,1))
I hope my advice will help you solve your task.
=IF(K:K="published","Live-API",IF(K:K="blocked","",IF(K:K="published","Live")))
Can I use the OR condition here?
Live-API or Live
Hello!
There are 2 conditions in your formula
K:K=”published”,”Live-API”
and
K:K=”published”,”Live”
The information you provided is not enough to understand your case and give you any advice, but this is wrong.
Thank you that worked perfectly. The calculating cell contains FALSE until all conditional are met. Is there a way FALSE can be blank until all conditions are me?
Thanks you
Hello!
If I understand your task correctly, You can use something like this formula —
=IF(A1>B1,A1,"")
Use "" instead of FASLE.
I have rows with some data in column A the same and need to combine data in column B whenever the data in column A is the same.
What formula can I use to summarize this data? A pivot table summarized the data, but it’s still shown in separate cells. I need to have one cell per column A answer.
Hello!
The IF function won't help you find the sum of the values.
If I understand your task correctly, you need to use the SUMIF function.
Say A1, A2, A3 and A4 cells with 100 characters
if you make the following formula
=IFS(1=1,CONCATENATE(A1,A2,A3,A4))
it throws a #Value error, I believe because A1...A4 exceeds the 255 text length
If you use
=IF(1=1,(A1 &A2 &A3&A4))
then it works as a charm
Anybody knows why this happens?
I mean by the second formula
=IF(1=1,CONCATENATE(A1,A2,A3,A4))
IF function is working, not IFS
Hello!
To combine long texts, I recommend using the CONCATENATE and CONCAT functions.
In a single CONCATENATE formula, you can concatenate up to 255 strings, a total of 8,192 characters.
Solve the the issue in the equation
=IF(OR(H8>=6,P8>=40%)2000, 2500, IF(OR(H8>=5,P8>=40%), 1500, 2000, IF(OR(H8>=4,P8>=40%), 750, 1500, IF(OR(H8<4,P8<40%),0,0))))
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, what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Hi ,
I wondered if you could help me with the following problem!
=IF($I$9=$A$3:$A$40,$C$3:$C$40,if($I$9=$B$3,$C$3:$C$40,0)
Can anyone please assist?
Hello!
Your formula is missing parentheses.
=IF($I$9=$A$3:$A$40,$C$3:$C$40,IF($I$9=$B$3,$C$3:$C$40,0))
Is this the problem?
i have a problem hope you can help me.
I want to construct a if statement such that
A1=10000
B1= 12 or 24 or 36
C1 =??
i want to automatically calculate if B1=12, C1=A1/2, if B1=24, C1=A1 and B1=36, C1=A1*1.5
Can someone help me?
Many thanks
Hello!
Please try the following formula:
=IF(B1=12,A1/2,IF(B1=24,A1,IF(B1=36,A1*1.5,"")))
or
=IFS(B1=12,A1/2,B1=24,A1,B1=36,A1*1.5)
I hope this will help
Any Body can correct this formula
=IF(ISNUMBER(SEARCH("011","012",014","017","061","076",077","078","085","089","092","095","099"
",C2)),"Cellcard") IF(ISNUMBER(SEARCH("010","015","016","069","070","081","086","087","093","096","098",C2)),"Smart")
IF(ISNUMBER(SEARCH( "088","097","071","031","060","066","067","068","090",C2)),"Metfone")
Hello!
Could you please describe your task in more detail? What result do you want to get? Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you
Can you help me? I need a formula that will do the following and will also need to meet multiple conditions:
If A1="Contract",B1="Cold" and C1<=1 year from today's date, display 7.5%, but if A1="Contract",B1="Cold" and C1<=2-4 years from today's date, display 3%.
A1 can be: Contract, Casino or Forms
B1 can be: cold or warm
and the percentages for all change based 1-4 years from today's date
I can't figure out how to incorporate all of the conditions within one formula.
Thank you so much!
Hello!
If I got you right, the formula below will help you with your task:
=IF(AND(A1="Contract",B1="Cold",DATEDIF(TODAY(),C1,"y")<=1),7.5%,IF(AND(A1="Contract",B1="Cold",OR(DATEDIF(TODAY(),C1,"y")>=2,DATEDIF(TODAY(),C1,"y")<=4)),3%,))
To find the difference between dates, use the DATEDIF function.
I hope my advice will help you solve your task.
Thank you for your help. I put the formula in and changed the cells and I am receiving a #NUM! error.
Hello!
The formula I sent to you was created based on the description you provided in your first request. You wrote the conditions in the second paragraph.
In order to prevent it from happening, please provide me with the detailed description of your task. Any examples of the source data and expected result would be of great help. It’ll help me avoid further confusions and find the right solution for you.
Hi
Please help me with following
If column A and B both contains apple the answer should be apple
And in the same way if both columns contains banana then also i need to get apple
If in column A is apple and in column B is banana answer should be banana and vise versa
Kindly help me using excel formula for this
Hello!
Example 3 "Using IF with AND & OR functions" above has the answer to your question.
Solution to Apple and banana conditions
=IF(OR(AND(A1="APPLE". B1="APPLE"). AND(A1="BANANA". B1="BANANA")). "APPLE". "BANANA")
=IF(OR(AND(A1="APPLE", B1="APPLE"), AND(A1="BANANA", B1="BANANA")), "APPLE", "BANANA")
I am working with Excell 2010 and I have a sheet like bellow
Cement, Brick, Sand (50 Item) = these all item will show as "Civil"
Wood, Board, Paint (15 Item) = these all item will show as "Carpentry"
Glass, Aluminium, Lock (10 item) = these all item will show as "Aluminium"
Paint, Polish, PaintWages (5 Item) = these all item will show as "Paint"
and many more.
I put a formula like it.
=IF(OR(A1="Aluminium Work (WIP)",A1="Aluminum & Glass work (HTL - Advance)",A1="Glass Materials ( WIP )"),"Aluminium",IF(OR(A1="Door Frame",A1="False Celling (HTL Febricator Advance)",A1="Flush Door",A1="Miscellaneous (Carpentry)",A1="Plastic Door",A1="Receiption decorational expenses (wip)",A1="Solid Door"),"Carpentry","")))
But After putting more then 25 logic it is not working, please help me.
Hello!
The IF function has a limit on the number of conditions. I recommend using a 2-column table in which each product has a corresponding name. For example, Cement - Civil, Brick - Civil. With the VLOOKUP function, you will insert the name from this table into your main table.
Read how to do a Vlookup in Excel.
I hope my advice will help you solve your task.
Hello,
I'm working on formula..
There is three input strings and based on that i need to find the answer... for i.e.. Box size - 8ft / Application - Chill / Door opening - Limited - for these inputs answer should come as C150e or any one input wrong answer should come as NA.
Hello!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=IF(A1*B1*C1=8,"C150e","NA")
What if i want to seperate date column into 5 combinations such as "0-30 days", "31-60 Days", "61-90 Days", "91-120 Days", "120 Days n Above".
Thanks
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question
Please try the following formula:
=IF((D1-TODAY())<=30,"0-30 days", IF(D1-TODAY()<=60,"31-60 days", IF(D1-TODAY()<=90,"61-90 days", IF(D1-TODAY()<=120,"91-120 days", "120 Days n Above") ) ))
Hi,
i am working with the Excel 2016 and I need a formula that will check if the the name in the first column is consistent with the names in the column alongside.
For instance:
Column B: Fruit, Vegetable; Beef.
Column C: Strawbarry, Apple; Potatoes, Salad greens Spinach, Turnips, Onions, beef, pork, sausage, veal, chicken
Fruit: strawbarry, apple;
Vegetable: Potatoes, Salad greens Spinach, Turnips, Onions,
Meat: beef, pork, sausage, veal, chicken
I need to check with a third column (D) if the column B is Fruit and the column C is Strawbarry, the check is correct --> "Ok"; while if the column B is Fruit and the column C is chicken the check will be negative --> "Non ok".
Please help me with this.
Thanks
Hello!
If I got you right, the formula below will help you with your task:
=IF(A1=(IF(SUM(--(B1={"strawbarry","apple"}))=1,"Fruit", IF(SUM(--(B1={"Potatoes","Salad","greens","Spinach","Turnips","Onions"}))=1,"Vegetable", IF(SUM(--(B1={"beef","pork","sausage","veal","chicken"}))=1,"Meat","" ) ))),"OK","NOT")
The fastest and correct way to determine which species a product belongs to is to use the VLOOKUP function. But your information is not enough to give you advice on its use.
Question No. 2
STELCO
Emp ID Emp Name salary Emp Type HRA TA PF Bonus Total
ST001 Amir Khan 8563 1
ST002 John Abraham 5320 2
ST003 Akshya 6586 3
ST004 Dileep 14521 4
ST005 Santhosh 4500 5
HRA Salary>=6000 20 %of sal 1000
TA Salary>=6000 2000 1500
PF Salary>=8000 14 %of sal 8% of sal
Hello!
For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Hi,
Amazing work Ablebits.com..
I try the formula here:
=IF(S4,"SUBMITTED","NOT SUBMITTED")
S4 represent the date of actual submission. I need to add another criteria "Ready by QC" in the formula.
If not mistaken, I need to have another date for "Ready by QC", correct?
Example:
Column A: Date Actual submission is: 20-09-2020
If the actual date key in, column C will turn to "Submitted", If empty, it will appear "Not Submitted"
Column B: Date sent for QC: 18-09-2020
Column C will turn to 'Ready by QC". If not key in the date column B, it will turn to submitted.
i am not sure if this the correct explaination.
Hello!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=IF(B1<>"","Ready by QC",IF(A1<>"","Submitted",""))
Hope this is what you need.
HI I M REQUIRED FOLLOWING FORMAT COMPLETE: -
I HAVE THREE COLMS
B=CW C=PW D=DIFFERENCE (CW-PW)
I HAVE REQUIRED A FORMULA:
IF D IS MORE THAN B SHOWS CALL, D IS MORE THAN C SHOWS PUT, IF B AND C FIGURES DIFFERENCE NOT DOUBLE THEN SHOWS SIDEWAYS.
Hello!
Your task is not completely clear to me. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you
Dear Sir/ Madam
I m using under mention formula
=IF(OR(B2>C2,C2C2,C2<B2, C2=B2), "put", "call","Sideways")) not works,
I want if C2=B2 difference is not double shows sideways also shows sideways
Hello!
Your formula is wrong. The information you provided is not enough to understand your case and give you any advice. Please provide me with an example of the source data and the expected result.
I am trying to put a formula together that is checking to see if 3 separate criteria's are met, and if they are met, it will populate data from another sheet in the same document. I've tried multiple formulas but they aren't exactly working. Please see below:
=IF(A9="REC 365", 1 * IF(C9="5 Year 4.99%",'REC Data Set'!A3:G25,"")) ---This one works but when i introduce the third criteria, it fails:
=IF(A9="REC 365", 1 * IF(C9="5 Year 4.99%", 1 * IF(E9="3.30",'REC Data Set'!A3:G25,"")))
I got closer with the AND argument but it doesn't work exactly, just gives me a value of false
=AND(A9="REC 365", AND(C9="5 Year 4.99%", AND(E9="3.30", 'REC Data Set'!A3:G25,"")))
Any help would be greatly appreciated. Thank you!
I have also tried this method:
=IF(AND(A9="REC 365", C9="5 Year 4.99%", E9="3.30"), 'REC Data Set'!A3:G25)
I'm getting a result of FALSE because something about this formula isn't true? I don't understand. All criteria is being met, this formula should at the very least evaluate as true.
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets.
I assume that you have a number in cell E9. You are comparing it to the text "3.3". Write down the condition E9 = 3.3
I hope my advice will help you solve your task.
Good Morning! Thank you for your reply!
I understand its difficult to decipher what I have going on here, and I appreciate your assistance.
I do have 3.30 in cell E9. I have also tried 3.3. Is there anyway you could (or be willing to) take a look at my document so you can see what I'm doing wrong? I have a formula that works for everything until it evaluates the last part for cell E9. Please see below:
=IF(A9="REC 365",IF(C9="5 Year 4.99%",IF(E9="3.3",'REC Data Set'!A3:G25,"")))
If I remove the E9 portion the formula works fine. I am out of ideas at this point and I've even gone in and tried referencing different cells and removing formatting from E9 and trying to use different cells instead of E9 to try everything I can think of. Please let me know if you are willing to take a look.
Thanks again either way!
I want to add that it has something to do with E9 being a number. When I change the formula to this:
=IF(A9="REC 365",IF(C9="5 Year 4.99%",IF(L10="G",'REC Data Set'!A3:G25,"")))
It works perfectly. For some reason the formula fails when it involves numbers of any sort.
L10 is just a random cell i used for testing and populated it with the value G. Any ideas on why it would fail with it being a number?
Thanks again!
how can u get result of many cells in one cell?
Hello!
I already wrote to you that you are comparing text with a number. Change the formula
= IF(A9 = "REC 365", IF(C9 = "5 Year 4.99%", IF(E9 = 3.3, 'REC Data Set'! A3: G25, "")))
Thank you for your help!
=IF(A9="REC 365",1*IF(C9="5 Year 4.99%",RECDataSet!A1,""),"time to go")
this can work
how can u get result? you asking for result with many cells in one cell.. check again
Hi,
For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.
I was able to do a work around of some sort that I'm okay with using for now. Id still like to know why its failing but I'm okay for now. This is my work around formula below:
=IF(A9="REC 365",IF(C9="5 Year 4.99%",IF(E9="A 3.30",'REC Data Set'!A3:G25,"")))
When the "A" is introduced, it works fine. Very strange.
Thanks for listening to me complain LOL
Hello,
I wonder if you can help? I am trying to get a score in excel. We have to score clients on the amount of outgoing they spend. For example if someone has an outgoing <2000 they get a score of 0, if they have -1000 to -5000 they get a score of 1, if they have -5001 to -10000 they get a score of 2.
Does that make sense? Basically scoring someone on their outgoings but I don't know how to do this with minus figures and when someone has a from to figure.
Any help would be great.
Hello!
Explain your terms. The amount 1500 refers to both a score of 0 and 1.
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.
Hi
I would want a formula to sum the multiple cells only in case whereever it is mentioned as roll up.
MatchID Status Amount
12345. Roll up HERE I need to have the sum the amount of below two rows
12345. 100
12345. 100
Hello!
I am not sure I fully understand what you mean. Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
I'm trying to work our a formula to add times to different delivery methods. for example . if seafreight add 42 days to dispatched date. if Airfreight add 14 days to dispatched date.
any ideas
Hello!
I hope you have studied the recommendations in the tutorial above. If I understand your task correctly, the following formula should work for you:
=IF(B1="seafreight",A1+42,IF(B1="Airfreight",A1+14,""))
Thanks your so much
Hello,
I have a table with multiple columns - Gift, Type, Date, Year Since Gift & Exempted.
Under Type there is a 2 option dropdown menu. - Charity and BPR.
I have got the Year since gift to automatically calculate the years.
What I would like to be able to do is have the Exempt column show "yes" or "no" if the gift is exempt.
If the Type column shows "Charity" it is exempt, so the exempt column would need to show Yes.
If the Type column shows "BPR" and it has been more than 2 years in the years since gift column it would be exempt, so would need to show "Yes". If its not exempt, I would like the column to show "No".
This is the formula that I have used, but it does not work correctly.
=IF(OR(AND(F14="BPR",H14>2),OR(F14="Charity",H14>=0)),"Yes","No")
Please can you advise.
Thank you!
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(OR(AND(F14="BPR",H14>2),AND(F14="Charity",H14>=0)),"Yes","No")
I hope this will help.
Hi,
Thank you for this!
I am however having the same problem. It works correctly for the BPR output but if I change the dropdown to Charity it does not change.
I was wondering if you could help me? I am trying to figure out what is wrong with this formula. I keeping getting the #VALUE! error.
=IF((AND(G2=OR("AF","SA"),(F2="R"))),"5.0%",IF(AND(G2=OR("AF","SA"),(F2=OR("S","F"))),"6.0%",IF((AND(G2=OR("NA","EU","AS","OC"),(F2=OR("R","S","F")))),"6.0%")))
Thanks in advance!
Hello!
Your formula is incorrect. Pay attention to how to write the AND and OR conditions correctly. See above Example 3 - Using IF with AND & OR functions.
G2=OR(“AF”,”SA”) - incorrect
OR(G2=“AF”,G2=”SA”) - correct
I was looking all over on how to code the IF AND OR formula for different shifts base on a time and your example on the grades showed me the way.
=IF(ISBLANK(D1),"",IF(AND(D1>=TIME(6,0,0),D1=TIME(14,0,0),D1<TIME(22,0,0)),"2nd Shift","Night Shift")))
There may be a better way, but this is working
Thanks
guys i've spend 5 hours. I just can't figure this out, so i seek help from you and god himself.
I want to make an IF statement between theese numbers.
7% $1,000.00
8% $10,000.00
9% $50,000.00
9.50% $100,000.00
how do I make if between 1000 and 9999 then show 7% or between 10000 and 49999 then show 8% etc.. etc...
all my love
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question
=IF(A1>=100000,9.5%,IF(A1>=50000,9%,IF(A1>=10000,8%,7% ) ) )
Hope this is what you need.
Thx for your fast reply. I just figured it out my self.
the "=IF" functions does not work. I ahve to use an "=IF(And(" function.
so the code gonna look like this
=if(and(K14=F12,K14=F13,K14=F14,K14=F15,K14=F16,K14=F17,K14=F18),E18,"Forkert beløb i j14"))))))))
Thx for helping it's much appreciated
Hello!
Explain what exactly does not work. How does this new formula relate to your problem?
I have another great question which is even harder to figure out.
I got this cummulative calculations which starts with
5% of all up to 10,000.00 $
10% of all between 100001 and up to 20,000.00 $
12% of all between 20001 up to 50,000.00 $
etc. etc..
how do I make that calcualtion so it won't keeping make 5% of all after 10k but still SUM the all the differnet percentages?
thx for your time and help. how can I share your knowledge with the community?
Hello!
You have not described all the conditions and the expected result, but I think the following formula will suit you:
=MIN(A1,10000)*5% + MAX(0,A1-MIN(MAX(0,A1-20000),30000) - MAX(0,A1-50000)-10000)*10% + MIN(MAX(0,A1-20000),30000)*12% + MAX(0,A1-50000)*12%
I hope it’ll be helpful.
Is there a way to make this Countif statement work?
=COUNTIF(C11:C112,"Low", AND D11:D112,"Work in Progress")
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. I am assuming you have not one but several conditions to count. Therefore, I recommend using the COUNTIFS function. Read this detailed instruction.
I hope this will help, otherwise please do not hesitate to contact me anytime.
I want to have solution of the following, if some one can help me:
Cell A1 = Professional
Cell A26 = sum of figures from cell A2 to A25
I want to have results in A30 that if cell A1 is equal to "Professional" and results of A26 is less than or equal to 400 than it should display the results of A26 otherwise 400 should be there,
Hope anyone of you can understand my query
Naeem Sabir
Please can someone help me with an excel formula? I have a cell E6 which has a drop down (Not Applicable, Fully Implemented, Mostly Implemented, Partly Implemented and Not Implemented). I want cell E7 to output 100%, 100%, 75%, 50% and 0% when E6 is Not Applicable, Fully Implemented, Mostly Implemented, Partly Implemented and Not Implemented respectively.
Hello!
Use an IF function with nested conditions. You can find detailed instructions in this article.
If there is anything else I can help you with, please let me know.
Hi, i want determine the latest date for this:
If: a1=complete, c1=complete, e1=complete,g1=complete, i1=complete, if all of these are in "complete status" i want to get the latest date: b1=2/1/2020,d1=3/2/2020,f1=4/2/2020,h1=5/1/2020,j1=6/1/2020,j1=7/1/2020
Hello!
You can find the highest date according to your criteria using the MAXIFS function.
=MAXIFS(A1:D1,A2:D2,"Complete")
Hope this is what you need.
If you write 5 column like this
A.Name
B. Source (broker,ref and direct)
C .product value
D. commission %
E. Commission amount( need formuls this column)
1 ) if any broker(column B) sale 1 product
Get commission (product value* 2%)=
2 ) any existing buyer through his refrence sale 1 product to any customer
Get commission 3 types
* 1 st deal = 10k
* 2 nd deal = 20k
* 3 rd deal = 30k
And
3) if direct sale any product
Get 0 commision
How to codition formula in one cell
Hello!
Your task is not completely clear to me. Explain condition 2 using your table data as an example. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Hi would you be able to help me with the below. I have 2 formulas that work separately however want to merge them into one formula.
=IF(OR(AND(SUM(W3:X3)>=100,Y3>=0), AND(SUM(W3:X3)<=100, Y3<=0)), "Yes", "No")
=IF(W3="","N/A", IF( X3= "", "N/A", IF(Y3="", "N/A")))
I would like to add into the first formula that if W3 or X3 or Y3 are blank, then it should return "N/A"
Hello
i have a list of names, scores, sex and statuses and here's my assignment:
if the "status" of the customer is "Faculty" then give it a value with "Dr. " plus a space plus First Name plus a space plus Last Name.
if the "status" of the customer is "student" and the "sex" is "M" then give it a value with "Mr. " plus space plus first name space plus last name
if the "status" of the customer is "student" and the "sex" is "F" then give it a value with "Ms. " plus a space plus first name plus space plus last name
please help me out with that one i am completely clueless on how to do it..
Hello!
Your question has already been asked here: https://www.ablebits.com/office-addins-blog/2014/12/03/excel-if-function-iferrror-ifna/comment-page-18/#comment-560805
Good morning Svetlana,
I really need some help on this,
How do i write a formula to convert every 0.30 to 1.
Assuming cell a1 contains 100, how do i count very 30 as 1.
I hope this makes sense and I really appreciate the assistance!!
Thank you in advance for the help!!
Kind regards,
Samuel
Hi Friends
Could you please help me to get the excel formula?
c11 = 50% of b11 or 100000, which one is lower
Thanks
Manik
Hello!
I recommend that you use the MIN function as described in this guide.
Hello, I am attempting to forecast employee availability based upon a singular calendar date.
Through conditional formatting I’ve created cells to be filled if they align with the start and end dates based upon a singular employee being able to forecast 10 different start and end dates columns.
I was able to get all 10 to feed into a singular cell based on a IF(OR(AND statement, however I will only get a 1 if true and 0 if false. For the entirety of the formula...
My end-state is that I would like to create a data validation that doesn’t allow any date to overlap any of the other dates.
My thought was if a cell yielded 1, great! If the cell went greater than 1, then the user would get an error message stating the date overlaps with another.
Unless you know a better solution? I am all ears, Grand Master Yoda :)
Please Help
Warm Regards, Michael
Hello!
Unfortunately, without seeing your data it is impossible to give you advice.
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Hi can anyone spot the error in the following formula? Thanks.
=IF(C$7<=55,"37%", IF(55<C$7<=60,"26%", IF(60<C$765,"12.5%"))))
It gives either 37% (for 55) as outcome.
Hello!
If I got you right, the formula below will help you with your task:
=IF(C$7<=55,"37%",IF(AND(55
Hi
Can any one apply the formula for given 2 conditions please.
Condition-1
Marks 20 to 40
Marks 41 to 60
Marks 61 to 80
Marks 81 to 100
Eligible Marks 30
Score (Marks) 30
% of Marks 100
Award for 1500
Eligible Marks 30
Score (Marks) 35
% of Marks 117
Award for 2500
Condition-2
Marks 20 to 40
Marks 41 to 60
Marks 61 to 80
Marks 81 to 100
Eligible Marks 45
Score (Marks) 45
% of Marks 100
Award for 2000
Eligible Marks 45
Score (Marks) 55
% of Marks 122
Award for 3000
Hello!
Sorry, I do not fully understand the task. ould you please describe it in more detail? What result do you want to get? Thank you!
Condition:1
Marks >=20 & Marks =20 & Marks =41 & Marks =41 & Marks <=60
% Marks 110 & Above
Eligible for Rs.3000/- reward
Hello!
Perhaps you can help me. I am hoping to write a IF + AND statement with multiple results.
A171 is "2" and A1 >53 AND <71 is "0". Thanks!
Hello!
Please reread the article above, it covers your case completely.
What does the condition mean - A171 is “2”?
I wish to write fx where A1>71= hypytension, A153=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))
However this does not work for me. Thanks
I wish to write fx where A1>71= hypytension, A153=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))
However this does not work for me. Thanks
Sorry meant that I wish the span of normal to be between 53.
My initial thought was fx= IF(A1>=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))
Hello!
Your terms change with every comment. Your conditions A1 <= 53 and A1 = 53 cannot be met at the same time. Perhaps this formula will suit you
=IF(A1>=71,"hypertension",IF(A1<=53,"hypotension","nornal"))
Pay attention to the following paragraph of the article above — Multiple IF statements in Excel
I have a condition kindly provide me the formula
upto 10 KM a company charges $20
above it per km a have additional charges is $2 per KM
if a person travel following km what was the formula
A B C D E F
1 18
2 10
3 24
Then what is the formula I put in B1
Hello!
If I understand your task correctly, the following formula should work for you:
=MAX(A1-10,0)*2+20
I hope it’ll be helpful.
Hi
is it possible to use VLOOUP function in multiple IF statements as below? already try cannot work.
need your advice if have another way to solve it.
thanks in advance.
=IF(EXACT(G5,"AGENT"),VLOOKUP(E5,MASTERLIST!C:G,2,FALSE), IF(EXACT(G5,"MINI AGENT"), VLOOKUP(E5,MASTERLIST!C:G,3,FALSE)), IF(EXACT(G5,"DROPSHIP"), VLOOKUP(E5,MASTERLIST!C:G,4,FALSE)))
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
However, the formula contains an error. Try to change it.
=IF(EXACT(G5,"AGENT"),VLOOKUP(E5,MASTERLIST!C:G,2,FALSE),
IF(EXACT(G5,"MINI AGENT"),VLOOKUP(E5,MASTERLIST!C:G,3,FALSE),IF(EXACT(G5,"DROPSHIP"),VLOOKUP(E5,MASTERLIST!C:G,4,FALSE))))
I hope it’ll be helpful.
Thank sir, formula work perfect!!
Hey Ablebits.
Thx for this amazing page. i've been using you so much. I'm unsure on how I post a question in a new topic. so feel free to move me in the right Direction.
I have this Formula
=if(and(D16=C4,D16=C5,D16=C6,D16=C7,D16=C8,D16=C9,D16=C10),B10,"Forkert beløb i D16"))))))))
How do I fast make changes to this, so that all the times it says "D16" I will change it to "K30"
Cheers
Hello!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:
=IF(AND(K30=C4,K30=C5,K30=C6,K30=C7,K30=C8,K30=C9,K30=C10),B10,"Forkert beløb i K30")
Trying to write a formula for calculation of: 25% of the quantity or (quantity x rate = ) Rs.5000 whichever is more
I have multiple sheets with similar information; students fill in one of the sheets but I want the last sheet to look at all the other sheets (at a specific cell) to see which one(s) are filled in and enter that number in the formula cell. The number will be the same regardless of the sheet information entered in. So regardless of which sheet the information was entered on, it should return a specific number.
This is one of the formulas I tried; but I received an error (#value!) - =IF((OR(AP!E5:S5=1, OA!G5:S5=1)), "1", "0"). I am looking at a range of cells in each sheet for a response.
I have to add 'if A2>=200,B2=200,B2<=0)),"999",A2/B2)
But, Can I make this formula in IFERROR form ? because in 'value in false' shows #DIV/0!
Thank you very much in Advance.
Sean
Hello!
The purpose of your formula is not entirely clear to me. However, your conditions B2=200 and B2<=0 cannot be met at the same time. If B2=0 you will get # DIV/0 error!
Hello Team, I want to know the formula if 35Km, 45Km, 65KM is their and I want to fixed $7 for 5Km per ride then what was the formula
hello Team, I'm a bit stuck I need your help. So what I'm trying to do is to put 'if' formula in this way:
I have 10 persons and 8 cars, when I put a person to work i want to be assign to a car, but when that person is off I want another person to be on that car. The problem that I have is that if I put the first person off the second person will take the car but the will double up.
My formula is in this way: =if(B=1, A, B=0, C). I also tried =if(B=1, A, B=0, C, C=1, D)
I think I would need a 'but' somehow but I don't get the hang of it.
Any ideas would be very helpfull
thank you
Hi, need help to create a formula that can add 7 numbers (my own given numbers) to generate 5 number combinations between 1 and 36.
Hello!
I am not sure I fully understand what you mean. Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.
Hi,
I need to help do this correction and needed to add more like this???
=IF(C4,(G4*H4*2+G4*J4*2)*L4),IF(C5,((((I5+H5)/2)*G5)*2+(G5*J5+0.141*K5))*L5)
Okay, I have a question. I am trying to pull data from one cell that has a lot of data. IF the data were consistent, I believe it would be easy. But it is not.
So, I have cell A2 for example the data in the cell is something similar to this:
Persons Cell Phone Number: 123-456-7890 (not everyone enters it that format and I can't 'force' it to be that way)
Persons Personal E-Mail Address: (again, not everyone types it out the same)
-Variations could be Persons Personal E-Mail Address: with or without a space before the start of the email
Persons E-Mail Address: with or without a space before the start of the email
All I want to do is easily pull out the persons email address and cell phone number. Here is an actual example of some of the data that could be in ONE cell (the data is not always in this order):
PERSONS E-MAIL ADDRESS: PERSONS.EMAIL@OUTLOOK.COM
________ (SM INITIALS)
PERSONS CELL PHONE #: (XXX) XXX-XXX
________ (SM INITIALS)
WHERE ASSIGNED TO (SSSSSS) : MR JIMMY SMITS
OFFICE/CELL PHONE NUMBER: (XXX) XXX-XXXX / (XXX) XXX-XXXX
E-MAIL ADDRESS: ANOTHER PERSONS EMAIL@OUTLOOK.COM
IF I AM UNABLE TO CONTACT MY OFFICE, I WILL CONTACT THE HELP DESK AT 1-800-XXX-XXXX.
Hello!
To extract e-mail from the text, use the formula:
=MID(A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23, (SEARCH("_",A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23+1)) - (SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23))
To extract a phone number from text, use the formula:
=MID(A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21, (SEARCH("_",A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21+1)) - (SEARCH("PERSONS CELL PHONE #:",A2,1)+21))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thanks, the problem is that only works if the data is all exactly the same. If the way they enter the title of the persons email address is PERSONS E-MAIL ADDRESS then it might work if all of the other data was in the same location, character wise, in each cell. Sometimes, however, they enter the title of the persons email address as PERSONS PERSONAL E-MAIL ADDRESS: sometimes they enter it as PERSONS E-MAIL ADDRESS: and then to top it off, if they put spaces after the : it throws it off even more.
Hello!
You can remove extra spaces using any of the methods indicated in this article. But if they enter data in the wrong place, the program cannot fix it.
However, by changing these formulas, you can extract any part of your text.
If there is anything else I can help you with, please let me know.
Hello Ma'am
how can i summary all the fail and pass in the same sheet? I want to separate all the fail and pass after using if function please help. I don't want to manual copy and paste the name the selected fail or pass thank you and GOD bless.
I have issues producing a function to fit in my amount cell.
Ok.
I have income from 3 sources Beans, Rice and Garri and I can only spend within the income received from each of these items. So I have an income section where I record income received from these items and their totals are in cell (A5, C5, E5 respectively). So For example, if I received 10,000frs as income from Beans, I cannot spend above the 10,000frs.
I now have an Expenditure section where I have a date column, a searchable list column where I can select the source of income (Beans, Rice, or Garri) i want to spend from at the time being and then a column for amount.
I want an IF function that when the amount of Expenditure of a particular item is more than the income received for that item, it should give an error message.
For example. If I received 10,000frs for Beans, and I put the first Expense in the amount of Expenditure section, 5,000frs, choosing Beans as source of income and now if I want to put the second Expenditure on a different date say 6,000frs still choosing Beans as source of income. It should return an error message because is more than 10,000frs income received from that item (Beans)
Please can someone help me out
Hi,
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.
Ok. Thanks for the reply Sir. Please How can I get to you may be privately for help in this.
I'm getting a FALSE status on the one logic.
=IF(AND(V8=1,Q8>7/31/2020,Q8<9/1/2020),R8,"")
Q8 is the date 9/17/2020
R8 is a dollar amount
Logic 3 keeps returning FALSE, but I can't figure out why.
I'll be copying this down a column where some of the dates fit the parameters.
Hello!
Here is the article that may be helpful to you: Using IF function with dates
Please try the following formula:
=IF(AND(V8=1,Q8 > DATE(2020,7,31), Q8 < DATE(2020,9,1)),R8,"")
or
=IF(AND(V8=1,Q8 > DATEVALUE("7/31/2020"), Q8 < DATEVALUE("9/1/2020")),R8,"")
I hope I answered your question.
They both worked! Thank you so much!!!
From looking through your site, I had tried the DATEVALUE, but missed using the " marks.
Thank you again!
hi all
i have some data like column a column b column c column d column e
a1 b1 c1 d1 e1 f1 g1 h1 i1 j1
1 2 3 4 5 6 7 8 9 10
if b1="a", then c1 should 2 d1 3 e1 3 like series should continue
if a1="a", b1="a", then c1 shoud start with 1, d1 2, e1 3 4 5 6 like
if f1 "a", g1 should start with 6,7,8,9,10............
A letter it may repeat and continues , but series should be continue.
ple provide the formula for above logic.
I want to apply a formula to print purchase order status is active or if print purchase order status is cancel then print cancel. please guide me what is the formula i need to apply to get result
Hi,
An Excel formula can change the value of a cell. But she cannot start printing the document or cancel printing. To do this, you need to use a VBA macro.
Please i need a formula for the following:
If "Abbasia" then add 30 minutes to the time (The time is in another cell) and if Maadi Please write the time as it is (The time is in another cell)
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question about IF function.
To add 30 minutes to the time, use the formula
A1+TIME(0,30,0)
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi! A non-techy person here and I need help for the formula for this please. (If B2 and C2 is blank then 0, if B2 and C2 has a value then D1+B2-C2) Thanks in advance!
Hi,
I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Please use the following formula
=IF(AND(B2="",C2=""),0,D1+B2-C2)
or
=IF(ISBLANK(B2:C2),0,D1+B2-C2)
I hope it’ll be helpful.
English Punjabi Hindi Math Science SST Comp Sci BELOW40% 90%&ABOVE
16 24 40 8 8 12 20 0 108 45.0 ENG,MAT,SST
24 28 40 10 18 12 26 0 132 55.0 ENG,PUN,MAT,SCI,SST HIN
12 6 6 8 10 12 14 0 54 22.5
16 22 10 4 10 4 22 0 66 27.5
18 20 30 14 16 18 16 0 116 48.3
14 22 30 14 16 18 16 0 114 47.5
WHICH FORMALUA APPLYED IN EXCLE TO GET ANSWER. PLEASE HELP ME
Hello!
I'm having a bit of a challenge on this issue. I have a list of 2 columns, A and B.
Column A has duplicates that I need to remove, but I need to "Textjoin" column B first.
> Forgive extra Parenthesis, I need them to be able to read it better.
Currently using "=IFS((A2=A3), (TEXTJOIN(", ",TRUE,B2,B3)))
However, the problem is that it leads to duplicate text joins, and it has a limit to how many values it's joining. I have it set as the first function of the column to start things out.
Formula #2 I came up with:
=IF((A2=A3), (TEXTJOIN(", ",TRUE,B2,B3)), (IFS((A2=A1), (TEXTJOIN(",",TRUE,E1, B2)))))
However, even this doesn't work as I want it to because it's merging cells multiple times. Not all of the groupings are by two. Some are 5 cells, some are 3, etc.
Formula #3 I came up with:
For this one to work I had to put in a value for the first cell in Column C. (I just set it =A2).
"=IFS((A3=A2), (TEXTJOIN(", ",TRUE,C2,B3)), (A3=A4), (TEXTJOIN(", ",TRUE, B3, B4)))"
Formula #3 produced the best results, but still adding cells incorrectly.
The reason for this; there are roughly 12K rows and I need to reduce it as much as possible. However, I don't have a month to go cell by cell confirming it joined correctly.
Hopefully you can pass on some assistance!
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. In which cells do you write these formulas? Please provide me with an example of the source data and the expected result.
Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Hello
Rate Quantity Result
2 2 4
2 Text Text
Text 2 2
Text1 Text2 Text2
I need the result like this help me with the formula please
2*2 = 4
2*Text = Text
Text*2 = 2
Text1*Text2 = Text2
All possibilities should be in one formula
In Cell I5 Create a drop down list with MON,TUE,WED, THU, FRI, SAT, SUN
In Cell I5 Select TUE from the drop down list
In Cell I6 Create a drop down list with BKLYN, QNS, NYC, BX, SI
In Cell I6 Select NYC from the drop down list
In Cell J5 Create an IFS formula that will result "# of Drivers working" on the DAY selected in Cell I5 and
the BORO selected in Cell I6
I know, how many drivers were working on the selected day and borough as I am following the chart but I have a problem with the formula. I don't know, how the formula will be since it requires me to have a formula in one cell with two different condition. Need help in formula.
MON in Bklyn, Nyc, Qns, Bx, Si is only 1 driver working
TUE all the same monday results
Wed all the same monday results
Thur all the same monday results
Fri all the same monday results
Sat all the same monday results
Sun all the same monday results
Only 1 driver was working when the condition met with any options if its Days or Borough they all have a same answer which is 1.
Thankyou.
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. Please check out the following article on our blog, it’ll be sure to help you with your task: Counting unique values with multiple AND / OR criteria
Hope you’ll find this information helpful. If there is anything else I can help you with, please let me know.
I am preparing examination results, that consist of thirteen subjects each subject has two scores, then i calculated the average for each subject by considering two score i need a help on how to find the result points by considering seven subjects that a student got higher marks.
Hello!
Please check out the following article on our blog, it’ll be sure to help you with your task: FILTER top n values based on criteria
If there is anything else I can help you with, please let me know.
Hi All,
Need a help in sorting out a problem. I'm trying to use the combination of IFS, AND, OR here.
(All values are dates)
Condition 1:
Today's date (C1) = 04-12-20
A1 = 04-12-19, 04-12-18
&
A1 = 04-02-21, 04-12-21
I want to merge these conditions into a single cell E2
If (C1-A1365,"Past 13+ Months") and if(A1-C190,"Fcst 4+ Months")
Problem is that, it is reading two conditions as same, if the value is 0, which I'm trying to avoid.
I would really appreciate some help on this by anyone.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF((C1-A1) > 365,"Past 13+ Months",IF((A1-C1) > 90,"Fcst 4+ Months",""))
I hope it’ll be helpful.
I am having a heck of a time trying to get an IF(OR(AND formula to work correctly. My rows contain maintenance requirements with varying frequencies or recurrence. I then have 15 date columns each holding the next projected date for each maintenance requirement. The statement below captures a search through 3 of the 15 columns to see if the projected maintenance falls prior to a planned maintenance time. I keep getting an #VALUE! error. Any help would be appreciated. I can't help but think it is something simple. I have had attempts where I can get it to return a "Y" for the first occurrence but nothing after that in subsequent years. Thx.
=IF(OR(AND(I12>$F$2,I12$F$2,J12$F$2,K12<$G$2)),"Y","")
Not entirely sure how the non-sensical formula in my last post managed to get in there. Note to self: Friday afternoon is no time for troubleshooting and analysis. It was clearly Beer O'clock and I was not paying attention to my inner voice. :)
Feeling bright and bushy tailed on this Saturday morning, I was able to solve the puzzle.
=IF(OR(AND(I12>F2,I12F2,J12F2,K12F2,L12<G2)),"Y","")
Aloha,
Can someone please check this formula? =IF(C3:D3=E3:K3), "BALANCE")
Hello,
I am trying to use the formula below, but it is not correctly categorizing my data and I cannot figure out why.
=IF((AND(A2>=TIME(7,0,0),A2<=TIME(18,0,0))),"Diurnal","Nocturnal")
While some categorizations are correct, others are not. For example one species was caught at 15:36 Army time, but is categorized as nocturnal when it should be diurnal. Any thoughts?
Also, as a fun bonus question: is there any way I could add a third option for species that come out at dawn out at dawn or dusk?
Hi all looking for help with and IF AND and Or function
im trying to get it to either times by .25 if both parameters are met or if only one then times by .5
And if none of the parameters are met then leave as is.
=IF(AND(J45="Tigress",M45>100000),O45*0.25,O45)*(OR(IF(J45="Tigress",O45*0.5,O45)))
Any help would be much appreciated
=IF(AND(J45="Tigress",M45>100000),O45*0.25,IF(OR(J45="Tigress",M45>100000),O45*0.50,0))
This should work
Explanation on the below
If J45=Tigress and M45 less than 100000, value in cell O45 will multiply to 0.25
If J45=Tigress or M45 is less than 100000, value in cell O45 will multiply to 0.50
If neither of the equation, the answer will be 0 - or you can change it to "Not found" instead of 0
Hello,
I'm working on a project with 3 criteria and my formula seems to super long.
=IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(K2=3,L2=8,M2>25),2,IF(AND(K2=6,L2=8,M2>25),4,IF(AND(K2=6,L2=8,M2>25),4,0)))))))))))))
Column K is fixed with 3 numbers = 3, 6 and 12
Column L could be between from 1-12
Column M could be between from 1-31
This is my formula so far. It's Super long. Maybe you can help me.
=IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(B2="KSA",K2=3,L2>2,L26,L28,M28,M20,M212,M22,L25,L28,M28,M20,M22,L22,L22,L22,L22,L22,L22,L225),2,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="KSA",K2=3,L2=8,M2>25),2,IF(AND(B2="KSA",K2=6,L2=8,M2>25),4,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="Jordan",K2=6,L2=8,M2>11,M225),4,IF(AND(B2="Jordan",K2=4,L2=8,M2>11),2,0))))))))))))))))))))))))))))))))))))
does any know
I want to write an argument statement that if a number in a certain column >-999 or<999 then send me an email.
does anyone know how I can write this?
i have to design a excel sheet for calculating printing cost, which involves multiple conditions
categories :paper size, quantity, paper quality (100 GSM), Lamination,etc., i have to calculate paper cost, plate cost, labour cost according to the inputs.
under paper size category there are diff sizes like
1.a4
2.a3
3.crown
4.double crown
5.dimmy
etc
under paper quality categery it also varies according to gsm like 100gsm,170gsm,300gsm etc
by looking up and combining paper size and gsm i have to calculate paper cost and remaining calculations labour printing cost, plate charges are also have to be calculated using combined inputs.
please suggest me formulas with example to use / provide me a model excel.
Thanksalot,
Cell G16 shows total.
I want G17 to display "Level One" if cell G16 falls between B4 on another sheet (number listed in cell B4 is 10-13). Need this nested with C4 (14-16), d4 (17-19), e4 (20-22), f4 (23-27), g4 (28-38)
HI
I NEED A FORMULAR THAT WILL HELP CHECK IF A STUDENT HAVE PASS ALL REQUIRED COURSE.
THANK YOU
Hi,
I am trying to create a formula with 3 different conditions.
If J3<5 and K3<5 it should calculate K3-J3+1
If J34 it should calculate 4-J3+1
If neither is true the result is 0
I have tried different variations o the following but nothing works:
@IF(OR(AND(J3<5,K3<5),k3-j3+1,AND(J34),=4-j3+1,0))
Any advice would be greatly appreciated. Thank you
ERROR in 2nd "If" condition I posted.
Should read: If J34, it should calculate 4-J3+1
ERROR in formula I posted.
Should read: @IF(OR(AND(J3<5,K3<5),=k3-j3+1,AND(J34),=4-j3+1,"0"))
I copied and pasted so I am not sure what happened. So sorry for any confusion!
OK so strange - after hitting Send the text changes in my post!
Where it says J34 it should say IF J3 is less than 5 and K3>4.
Hope the 3rd time is a charm! Thanks again.
NEVER MIND - I figured out by studying at least 1,000 questions already answered here over the past few days.
Correct formula: =IF(AND(J3<=4,K3<=4),K3-J3+1,IF(AND(J34),4-J3+1,0))
Thanks to everyone who answers strangers very complex problems on this site. AMAZING!
And once again my formula changed upon posting.
If anyone needs the correct formula - respond to this and I will try to write it again with some other method
if-or(L59>L60,L59>L61,L59>L62),''yes'',''no'' which button to click on the keyboard in order to get to yes or no. i am learning on line.please help
Hi can u tell me how to use if and or function to get depreciation value with sln, dB and Syd method for the same data in same cell.
Hi!
I am trying to check for a valid response for a variable (P2), the code/conditions I have are meant for EpiInfo but I am having so much trouble getting the formula translated and working in Excel. Any help is greatly appreciated!
The conditions are:
P1=1 AND P2>0 AND P2<8
OR
P1=2 AND (P2=0 OR P2="." OR P2=99)
The returned value is '1' and all ELSE is '2'
HOW TO SOLVE THE PROBLEM ?
20% ANS = 20
=TEXT(P2,".00") This correct or ......
im trying to see if there is a way to detect a zero in cells b2 to b9 if there is i want it to execute one function or the other how can i do this (started self teaching best to over explain thank you)
I need to compare grades in two cells. like if D6=A+ and D7=A, than "A+". means show higher grade. grades are A+, A, B+, B, C, D and F. to get final grade, I need to compare two grades. if both grades are same, final grade will be same. if grades are different with one step, final grade will be higher one. if grades are different, with two steps, final grade will be in between. if grades are different and apart from each other, final grade will be in between higher one. examples given below:
example 1 (same)
A+ and A+ = A+
A and A = A
C and C = C
example 2 ( different/higher)
A+ and A = A+
A and B+ = A
D and C = C
example 3 (2 step different/ in between)
A+ and B+ = A
A and B = B+
D and B = C
example 4 ( apart/ in between higher)
A+ and B = A
A and C = B+
D and B+ = B
Please guide formula
Hello,
I want to copy a formula to downward cells, the first value to be changed cell to cell but the second should copy with the same value to all down cells:
Formula: B77-C4, to the down cell the formula should change to B78-B4, B79-B4 etc.
I wish someone can help
Regards.
Hi All,
can you help me for below.
i have 3 condition,
region,
manager,
more then 50 days,
so i have to find
region : Delhi.
manager : Alex.
then less then 50 days how many business done by alex.
after (region=delhi)(manager=alex)(days=<50)(total business done).
i tried if and sumif but i didn't get correct answer
HELLO ! Can someone help me PLEASE.
i have 6 Names in 3 DIFFERENT CELL: in cell A1"Emo/nonEmo", in cell B1"Bio/nonBio", in cell C3"Sio/nonSio". I want in cell A4 to display automaticaly miscellaneous combination between 3.
For example =IF(a1=bio,b1=nonBio,c1=nonSio) in A4 display"Anorganic". Thk
I can't seem to figure out how to fix this formula. Any thoughts?
=IF(AND(F6=”N’,G6=”N”,E6/2, IF(AND(F6=”Y”,G6=”N”,(E6/2)*0.75, IF(AND(F6=”N”,G6=”Y”,E6, IF(AND(F6=”Y”,G6=”Y”,E6*0.875,))))
If F6="N" and G6="N" then E6/2
If F6="Y" and G6="N" then (E6/2)*0.75
If F6="N" and G6="Y" then E6
If F6="Y" and G6="Y" then E6*0.875
Example:
e6=3780 f6=n g6=n then answer should be 1890
e6=3780 f6=y g6=n then answer should be 1417.5
e6=3780 f6=n g6=y then answer should be 3780
e6=3780 f6=y g6=y then answer should be 3307.5
Can someone help me to create a formula for this, please
Payment Type - FB
Amount - 100
I want to get this if the payment type is FB it should be equal to the amount
if not FB then 0
Hi,
I'm horrible with formulas and I need help. I need to put a dollar amount in a cell based on data on a different cell. This is what I am trying to do;
If row D = MB,CB.DB I want row I to show $2000, if row D = MB+,CB+,DB+ I want row I to show $3500 and so on. What formula would I need to use? Thanks in advance!
Hi,
Can someone please help me with this formula.
=if(F3="emergency loan"=1*D3,if(F3="short loan"=2*D3,if(F3="normal loan"=3*D3,if(F3="development loan"=5*D3))))
Essentially, if cell f3 reads a certain type of loan, i'm supposed to multiply it with cell d3 and the figure next to it. I'm stuck and frustrated.
Any help would be appreciated. Thanks in advance
A1=B1 B1=48 C1=62
A2= (very long formula) B2=A5 C2=(A5 in case A1=C1)
A3= (very long formula)
A4= (very long formula)
A5=A1*sum (A2:A4)
How can I put a formula for C2 since it is so hard to repeat all the formulas in A2 to A4? Please help me with this.
I need to return 100% in a cell for cells B2/A2. If B2 = 0, and A2= 0, I want the result to be 100% as a goal attainment returning 100% in C2.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(AND(B2=0,A2=0),100%,B2/A2)
I hope it’ll be helpful.
Hi! Can anyone please help me in the following situation:
Delivery charges are based on two types of services i.e Overnight & Economy
Barcode of both are like OVN123456 & ECO123456
If weight is <=1 kg delivery charges for OVN are 100 & For ECO are 70
If weight is <=3 kg delivery charges for OVN are 175 & For ECO are 150
I want that Delivery charges cell check if B3 has code OVN and E3 is having weight <=1 then show 100 OR if B3 has code ECO and weight in E3<=1Kg it shows 70.
I will be very thankful.
I'm trying to get two texts with the same number value...
home=0.5
office=0.5
any other entry=0
any thoughts on how to do that?
=IF(B5="home"or"office", 0.5,0) didn't work
Hello!
Please try the following formula:
=IF(OR(B5="home",B5="office"),0.5,0)
I hope it’ll be helpful.
Hello!
The formula below will do the trick for you:
=IF(OR(B5="home",B5="office"), 0.5,0)
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
Our clients have 4 scenarios that produce different outcomes. I am trying to produce a formula for those outcomes -
Columns I & J are the scenario factors, K is the outcome
I2=Y J2=N K2=N
I2=Y J2=Y K2=Y
I2=N J2=Y K2=Y
I2=N J2=N K2=N
I have tried the following, using " " for the Y & N variables, also trying it without the " " and the outcome produces #NAME? with both formulas. I'm sure there is something easy I am missing.
=IF(OR(AND(I2=Y,J2=N)),N,IF(OR(AND(I2=Y,J2=N)),Y,IF(OR(AND(I2=N,J2=Y)),Y,IF(OR(AND(I2=N,J2=N)),N))))
Hello!
One simple formula describes your 4 scenarios
=IF(J2="N","N","Y")
If you have other questions, don't hesitate to ask.
Hi Everyone
I am trying to write a formula that has few IF conditions eg:
if A1>=25 (age), then if B1=>£8.91, B1 otherwise £8.91 (it does work when A1 (age ) is = 25, if I change it to any other age it throws an error. This is one part of a formula
the full formula should calculate:
if A1>=25 (age), then if B1=>£8.91, B1 but otherwise £8.91, if A1=21, then if B1>8.36, B1 or £8.36
is it possible to calculate? I am really struggling with this, any help appreciated
Hello!
If the number 9 is written in cell B1, then no Excel formula can change the value in B1 to 8.91. This is only possible with a VBA macro.
I am trying to create an IF/THEN from a drop down list.
C1 would have the drop down LIst: Hamburgers, hot dogs, nachos, pizza
So the cell with the formula would need to perform: IF C1 is hamburgers, $5. If C1 is hot dogs, $2. if C1 is nachos, $4. if C1 is Pizza, $10.
Can anyone help give me a framework?
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(C1="hamburgers",5,IF(C1="hot dogs",2,IF(C1="nachos",4,IF(C1="pizza",10,""))))
In Excel 2016 and above, you can use the IFS function
=IFS(C1="hamburgers",5,C1="hot dogs",C1="nachos",C1="pizza",10)
If there are a lot of products and conditions, I recommend searching for the price using the VLOOKUP function.
I hope my advice will help you solve your task.
Thank you! I will try this out
Sir I recruiting some employees for my office. I recevied many applications and I have prepared a list in excel there is a condition that the applcant shouldn't be over the 40years of age and not less than 18years. I have entered the date of birth in column b and calculated the age in column c now i have the values some thing like 30year 2months and 4days now I need a formala to be entered in column D to find out that if the applicant is overage, within age limit or is he underage....
Hello!
Please check out the following article on our blog, it’ll be sure to help you with your task: How to highlight ages under or over a particular age.
If there is anything else I can help you with, please let me know.
Hi ,
Could you please help me to find formula for below Question ?
if Value of cell B is greater than and equal to 95 , its cell A*3% .
If Value of cell B is greater than and equal to 85 & less than 95 , its cell A*2.5% .
If Value of cell B is greater than and equal to 80 & less than 85 , its cell A*2% .
If Value of cell B is greater than and equal to 70 & less than 85 , its cell A*1% .
Please help me out .
Hello!
Please reread the article above, it covers your case completely. Pay attention to the following paragraph of the article above — Using multiple IF statements in Excel.
I hope it’ll be helpful.
Please let me know how to write following
1 to 30 = Manager
31 to 45 = Regional Head
46 to 60 = Head of Sale
61 to 90 = Credit Operation Team
91 and above = Recovery Team and Legal
1 and below = Before Over Due
Hi,
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
I kindly ask you to have a closer look at the following paragraph: Using multiple IF statements in Excel (nested IF functions)
I hope it’ll be helpful.
=IF(Index!D2="EXISTING",(ROUND(IF(H73250000,H73500000,H731000000,112500+(H73-1000000)*30/100,0)))),0)),(ROUND(IF(H73250000,H73500000,H73750000,H731000000,H731250000,H731500000,112500+(H73-1500000)*30/100,0)))))))