Excel IF statement with multiple conditions

Svetlana Cheusheva

by Svetlana Cheusheva , updated on March 22, 2023

The tutorial shows how to create multiple IF statements in Excel with AND as well as OR logic. Also, you will learn how to use IF together with other Excel functions.

In the first part of our Excel IF tutorial, we looked at how to construct a simple IF statement with one condition for text, numbers, dates, blanks and non-blanks. For powerful data analysis, however, you may often need to evaluate multiple conditions at a time. The below formula examples will show you the most effective ways to do this.

How to use IF function with multiple conditions

In essence, there are two types of the IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of your IF formula, you should use one of these functions:

To better illustrate the point, let's investigate some real-life formulas examples.

Excel IF statement with multiple conditions (AND logic)

The generic formula of Excel IF with two or more conditions is this:

IF(AND(condition1, condition2, …), value_if_true, value_if_false)

Translated into a human language, the formula says: If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false.

Suppose you have a table listing the scores of two tests in columns B and C. To pass the final exam, a student must have both scores greater than 50.

For the logical test, you use the following AND statement: AND(B2>50, C2>50)

If both conditions are true, the formula will return "Pass"; if any condition is false - "Fail".

=IF(AND(B2>50, B2>50), "Pass", "Fail")

Easy, isn't it? The screenshot below proves that our Excel IF /AND formula works right:

Excel IF statement with multiple AND conditions

In a similar manner, you can use the Excel IF function with multiple text conditions.

For instance, to output "Good" if both B2 and C2 are greater than 50, "Bad" otherwise, the formula is:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")

Excel IF function with multiple text conditions

Important note! The AND function checks all the conditions, even if the already tested one(s) evaluated to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.

In practice, a seemingly correct IF statement may result in an error because of this specificity. For example, the below formula would return #DIV/0! ("divide by zero" error) if cell A2 is equal to 0:

The avoid this, you should use a nested IF function:

=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")

For more information, please see IF AND formula in Excel.

Excel IF function with multiple conditions (OR logic)

To do one thing if any condition is met, otherwise do something else, use this combination of the IF and OR functions:

IF(OR(condition1, condition2, …), value_if_true, value_if_false)

The difference from the IF / AND formula discussed above is that Excel returns TRUE if any of the specified conditions is true.

So, if in the previous formula, we use OR instead of AND:

=IF(OR(B2>50, B2>50), "Pass", "Fail")

Then anyone who has more than 50 points in either exam will get "Pass" in column D. With such conditions, our students have a better chance to pass the final exam (Yvette being particularly unlucky failing by just 1 point :)

Excel IF function with multiple OR conditions

Tip. In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i.e. a cell can be "this" or "that"), then you can build a more compact formula using an array constant.

For example, to mark a sale as "closed" if cell B2 is either "delivered" or "paid", the formula is:

More formula examples can be found in Excel IF OR function.

IF with multiple AND & OR statements

If your task requires evaluating several sets of multiple conditions, you will have to utilize both AND & OR functions at a time.

In our sample table, suppose you have the following criteria for checking the exam results:

If either of the conditions is met, the final exam is deemed passed.

At first sight, the formula seems a little tricky, but in fact it is not! You just express each of the above conditions as an AND statement and nest them in the OR function (since it's not necessary to meet both conditions, either will suffice):

OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Then, use the OR function for the logical test of IF and supply the desired value_if_true and value_if_false values. As the result, you get the following IF formula with multiple AND / OR conditions:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

The screenshot below indicates that we've done the formula right:

IF with multiple AND & OR statements

Naturally, you are not limited to using only two AND/OR functions in your IF formulas. You can use as many of them as your business logic requires, provided that:

Nested IF statement to check multiple logical tests

If you want to evaluate multiple logical tests within a single formula, then you can nest several functions one into another. Such functions are called nested IF functions. They prove particularly useful when you wish to return different values depending on the logical tests' results.

Here's a typical example: suppose you want to qualify the students' achievements as "Good", "Satisfactory" and "Poor" based on the following scores:

Before writing a formula, consider the order of functions you are going to nest. Excel will evaluate the logical tests in the order they appear in the formula. Once a condition evaluates to TRUE, the subsequent conditions are not tested, meaning the formula stops after the first TRUE result.

In our case, the functions are arranged from largest to smallest:

=IF(B2>=60, "Good", IF(B2>40, "Satisfactory", "Poor"))

Naturally, you can nest more functions if needed (up to 64 in modern versions).

Nested IF statement in Excel

Excel IF array formula with multiple conditions

Another way to get an Excel IF to test multiple conditions is by using an array formula.

To evaluate conditions with the AND logic, use the asterisk:

IF(condition1) * (condition2) * …, value_if_true, value_if_false)

To test conditions with the OR logic, use the plus sign:

IF(condition1) + (condition2) + …, value_if_true, value_if_false)

To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

For example, to get "Pass" if both B2 and C2 are greater than 50, the formula is:

=IF((B2>50) * (C2>50), "Pass", "Fail")

IF array formula with multiple AND conditions

In my Excel 365, a normal formula works just fine (as you can see in the screenshots above). In Excel 2019 and lower, remember to make it an array formula by using the Ctrl + Shift + Enter shortcut.

To evaluate multiple conditions with the OR logic, the formula is:

=IF((B2>50) + (C2>50), "Pass", "Fail")

IF array formula with multiple OR conditions

Using IF together with other functions

This section explains how to use IF in combination with other Excel functions and what benefits this gives to you.

Example 1. If #N/A error in VLOOKUP

When VLOOKUP or other lookup function cannot find something, it returns a #N/A error. To make your tables look nicer, you can return zero, blank, or specific text if #N/A. For this, use this generic formula:

IF(ISNA(VLOOKUP(…)), value_if_na, VLOOKUP(…))

If the lookup value in E1 is not found, the formula returns zero.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return blank:

If the lookup value is not found, the formula returns nothing (an empty string).

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A return certain text:

If the lookup value is not found, the formula returns specific text.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A error in VLOOKUP

Example 2. IF with SUM, AVERAGE, MIN and MAX functions

To sum cell values based on certain criteria, Excel provides the SUMIF and SUMIFS functions.

In some situations, your business logic may require including the SUM function in the logical test of IF. For example, to return different text labels depending on the sum of the values in B2 and C2, the formula is:

=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))

If the sum is greater than 130, the result is "good"; if greater than 110 – "satisfactory', if 110 or lower – "poor".

Using the IF function with SUM

In a similar fashion, you can embed the AVERAGE function in the logical test of IF and return different labels based on the average score:

=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))

Assuming the total score is in column D, you can identify the highest and lowest values with the help of the MAX and MIN functions:

=IF(D2=MAX($D$2:$D$10), "Best result", "")

=IF(D2=MAX($D$2:$D$10), "Best result", "")

To have both labels in one column, nest the above functions one into another:

=IF(D2=MAX($D$2:$D$10), "Best result", IF(D2=MIN($D$2:$D$10), "Worst result", ""))

Using IF together with the MIN and MAX functions

Likewise, you can use IF together with your custom functions. For example, you can combine it with GetCellColor or GetCellFontColor to return different results based on a cell color.

In addition, Excel provides a number of functions to calculate data based on conditions. For detailed formula examples, please check out the following tutorials:

Example 3. IF with ISNUMBER, ISTEXT and ISBLANK

To identify text, numbers and blank cells, Microsoft Excel provides special functions such as ISTEXT, ISNUMBER and ISBLANK. By placing them in the logical tests of three nested IF statements, you can identify all different data types in one go:

=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", "")))

IF with ISNUMBER, ISTEXT and ISBLANK

Example 4. IF and CONCATENATE

To output the result of IF and some text into one cell, use the CONCATENATE or CONCAT (in Excel 2016 - 365) and IF functions together. For example:

=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

=CONCAT("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))

Looking at the screenshot below, you'll hardly need any explanation of what the formula does:

Using IF and CONCATENATE

IF ISERROR / ISNA formula in Excel

The modern versions of Excel have special functions to trap errors and replace them with another calculation or predefined value - IFERROR (in Excel 2007 and later) and IFNA (in Excel 2013 and later). 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.

For example, to replace the "divide by zero" error (#DIV/0!) with your custom text, you can use the following formula:

=IF(ISERROR(A2/B2), "N/A", A2/B2)

Using IF together with ISERROR

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!

Practice workbook for download

You may also be interested in

Table of contents

4530 comments

Marlon says:
2024-09-06 at 3:49 pm

Trying to Grab more than one Row of info at once if one column is true and another column is true give me a value if false =0
this works
=IF((Supplies2024!B5="Wolf")*(Supplies2024!D5="Housewares"),Supplies2024!J5,0)
but when I try to get more than one row, I can't find the formula to make this work
=IF((Supplies2024!B2:B5="Grizzly")*(Supplies2024!D2:D5="Housewares"),Supplies2024!J2:J5,0)

Alexander Trifuntov (Ablebits Team) says:
2024-09-09 at 9:25 am

Hello Marlon!
If I understand your task correctly, to get multiple search results at once, this article may be helpful: How to Vlookup multiple values in Excel with criteria.

NKGAUR says:
2024-08-10 at 5:41 am =IF(OR(D5>=300,D5-I6,D5>=1000,"700",0)) BUT NOT SHOW 700 Alexander Trifuntov (Ablebits Team) says:
2024-08-19 at 9:41 am

Hi! Sorry, it's not quite clear what you are trying to achieve. Maybe this article will help: Excel IF OR statement with formula examples.

Swapnil says:
2024-08-09 at 9:24 am

i have trying to "if formula with Full Columns but not apply I.e .. Last Move Days status we need status here :-
0 NOT aging
0 NOT aging
1 NOT aging
3 Clear Aging
3 Clear Aging
3 Clear Aging
3 Clear Aging
3 Clear Aging
3 Clear Aging
3 Clear Aging

Alexander Trifuntov (Ablebits Team) says:
2024-08-19 at 8:47 am Please clarify your specific problem or provide additional information to understand what you need. Mohamed Ibrahim says:
2024-08-05 at 5:59 pm Why the following formula doesn't work: =IF(AC2>0,"AC2*20%+7500",IF(AC2<0,"(AC2+30,000)15%+3000")) Alexander Trifuntov (Ablebits Team) says:
2024-08-19 at 8:15 am

Hi! I don't know what result you want to get. But if you want to perform calculations, don't use quotation marks. Also don't use thousands separators in numbers. I recommend reading this guide: Nested IF in Excel – formula with multiple conditions.

Pinku Ghosh says:
2024-07-26 at 6:45 am Just need your support to formulate the following : If A 0.55, B=3 Alexander Trifuntov (Ablebits Team) says:
2024-07-26 at 7:51 am Srinivasan says:
2024-07-18 at 3:24 pm

17-07-2024 15:36:05 above mentioned date & time format. How can i set a formula for after 15:00:00 is Without TAT (Red Highlight), before 14:59:59 is Within TAT (Green Highlight) can you please help me on this

Lucy says:
2024-07-15 at 2:52 am

If I have three option like my student get 3 point mention good and if he get's 2 point then 10 and if he got 1 point then 5 and 0 point then 15 "How I can implement
formula in excel sheet

Alexander Trifuntov (Ablebits Team) says:
2024-07-15 at 8:37 am

Hi! If I understand your task correctly, this article may be helpful: Nested IF in Excel – formula with multiple conditions. For example: =IF(A1=3,"good",IF(A1=2,10,""))

DIPAK says:
2024-07-15 at 2:00 am

Is half day and the working is 230 is full day and working is 490 is half day working with 230 then calculate with 230/240 and is full day working with 490 then calculate with 490/480 give me this condition formula

Alexander Trifuntov (Ablebits Team) says:
2024-07-15 at 8:32 am

Sorry, I have no idea exactly what the task is. As it's currently written, it's hard to tell exactly what you're asking.

stacey says:
2024-07-02 at 11:28 am

Hello, I am trying to do an if/or function for the following:
=if(c2d2,c2-d2,"")
I have typed it as =if(=or(c2d2,c2-d2,"")) what am I doing wrong? Please help.

stacey says:
2024-07-02 at 11:30 am that should read c2 less than d2 and c2 greater than d2 Alexander Trifuntov (Ablebits Team) says:
2024-07-02 at 11:53 am These two conditions cannot be fulfilled at the same time. Alexander Trifuntov (Ablebits Team) says:
2024-07-02 at 11:52 am

I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Perhaps your formula looks like this: =if(c2 Zishan Ali says:
2024-07-08 at 12:25 pm

Use formula according to it's orignal style instead of adding = after the (
=IF(OR(B2>50, B2>50), "Pass", "Fail")

Alexander Trifuntov (Ablebits Team) says:
2024-07-08 at 1:15 pm Hi! Why do you repeat the same condition twice? Ems says:
2024-07-02 at 2:44 am

Hello can you help to simplify this formula
=IF(F3=80,20,IF(F3=81,21,IF(F3=82,22,IF(F3=83,23,IF(F3=84,24,IF(F3=85,25,IF(F3=86,26,IF(F3=86,26,"0"))))))))

Alexander Trifuntov (Ablebits Team) says:
2024-07-02 at 6:38 am

Hi! You can simplify nested IF formula by using IFS function. You can find the examples and detailed instructions here: The new Excel IFS function instead of multiple IF.
According to your data, you can also use IF AND function: =IF(AND(F3 > =80,F3 < =86),F3-60,0)

Eve says:
2024-06-24 at 6:07 pm

Hello, I am looking for help to write a formula based on nursing staffing.
Here is the example I need help with creating a formula for in a spreadsheet The census is 30 and there are 5 registered nurses and 1 licensed practical nurse working … the formula I need help with would calculate 1 nurse and 1 licensed practical nurse work together take 9 patients. (The census of 30 patients minus the 9 patients the 2 staff take is 21). Therefore the remaining 4 registered nurses are split between the remaining 21 patients (so 3 nurses have 5 patients and 1 nurse would have 6 patients). My spreadsheet has one column (cell) for the census, 1 cell for RN, and 1 cell for LPN.
I need to be able to calculate the ratio for this rule with an if function, because there is not always an LPN scheduled to work, so when a 1 is entered into the LPN cell, it should count a RN & LPN working together with 9 patients. (If no LPN is working, the RNs are divided among the unit census). If someone can please help me, I have tried looking up help for this! I am not sure that an “if” rule is correct or how to proceed figuring this out.

Alexander Trifuntov (Ablebits Team) says:
2024-06-25 at 6:51 am

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.

Paheli says:
2024-06-21 at 6:08 am

I have 3 criteria and 9 condition with same criteria in different column for incentive calculation. then which formula we can try to do smooth work. Exp.:- If quarterly target is 5 lakhs, and achievement is 5.2 lakhs (104%
achievement) then MR will be eligible for Group B incentive that is 1.20% of the
quarterly sale that is 6240 Rs. In that case we have 3 group 3 different condition and in three Group we have another condition with achievement. so pls suggest Achievement Terms Group A Group B Group C
2.5 L
95-99.99% 0.50% 0.60% 0.75%
100-104.99% 1% 1.20% 1.50%
105-109.99% 1.25% 1.40% 1.75%
>110% 1.50% 1.80% 2%

Alexander Trifuntov (Ablebits Team) says:
2024-06-21 at 11:52 am

Hi! You can create many conditions for IF function as described in this guide: Nested IF in Excel – formula with multiple conditions. You can also use the information and examples from the article above. Create a separate formula for each group. Or you can combine these formulas if you make a group selection and then specify a calculation for each group. For example: =IF(G1="Group A",IF(AND(H1>95%,H1<99.99%),0.5%, IF(AND(H1>100%,H1<104.99%),1%,"")), IF(G1="Group B", IF(AND(H1>95%,H1<99.99%),0.6%, IF(AND(H1>100%,H1<104.99%),1.2%,""))))

Hi says:
2024-06-10 at 1:51 pm

Hello, thanks for the great work I have an exercise please and I need help to apply a discaount on excel. Regarding the discounts to apply:
6% if the customer is a wholesaler and bought more than € 10,000 in goods
3% if the customer is an individual and bought more than € 10,000 in merchants
0% if it is a retailer or if the customer bought less than € 10,000 while being wholesaler/individual. Which function can I use please?

Alexander Trifuntov (Ablebits Team) says:
2024-06-10 at 2:22 pm

Hello! All the necessary information is in the article above. For multiple conditions, you can use a nested IF function. Read the following paragraph of the article above: Multiple nested IF statements. You can also find useful information in this article: Nested IF in Excel – formula with multiple conditions.
If each criterion of a nested IF function includes two conditions, you can use the AND function for those conditions as described in the paragraph above: Multiple AND conditions, and in this article: Multiple IF AND statements in Excel.
This should solve your task.

Hiwa says:
2024-07-26 at 10:31 pm Thank you ,i need it Ben says:
2024-05-09 at 11:22 am

Looking to do something similar Where multiple duplicates of transactions but need it to group the customers then show the first payment is declined and last payment is approved. What would be the best way to complete this task ? Columns
Decision = Decline or approves
Date
Customer email
Count of orders

Alexander Trifuntov (Ablebits Team) says:
2024-05-13 at 7:27 am

Hi! Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following guide: How to find duplicates in Excel: identify, highlight, count, filter.

sha says:
2024-05-08 at 2:17 pm

Hi, need help with these:
Between 94.99% to 85% = 5
95.00% - 99.99% = 4
100.00% - 104.99% = 3
105.00% - 109.99% = 2
110% and above | Below 85% = 1

Alexander Trifuntov (Ablebits Team) says:
2024-05-08 at 2:19 pm

Hi! The answer to your question can be found in this article: Nested IF in Excel – formula with multiple conditions.

Chandra says:
2024-05-03 at 7:24 am

hi,
if one cell value equal or less than 14, is answer is "0"
if one cell value more than 14 is answer their value actual.

Alexander Trifuntov (Ablebits Team) says:
2024-05-03 at 9:53 am Hi! The answer to your question can be found in this guide: IF formula for numbers. Kaizz Audrey Bobadilla Magpantay says:
2024-04-23 at 10:08 am

Formula for multiple LEFT Function in referencing to 1 cell only. The results either should be 2 digits,3 digits, 4 digits and 5 digits/

Alexander Trifuntov (Ablebits Team) says:
2024-04-23 at 10:23 am

Hi! Your question is not clear. I think you can solve the problem yourself if you use this manual: Excel LEFT function with formula examples. If you can't do this, give an example of source data and desired results.

Riz says:
2024-04-18 at 5:41 pm

Hi guys need help.
Im looking what function to use If A1 = “5x9” it should follow a 30 mins break and if more than 30 mins it should result to 1. if not, 0.
Then, if A1= “4x11” it should follow a 40 mins break and if more than 40 mins it should result to 1. if not, 0.

Alexander Trifuntov (Ablebits Team) says:
2024-04-19 at 7:49 am

Hi! For multiple conditions you can use the nested IF function. I can't recommend you a formula, as not all your conditions are clear to me. For example "if more than 30 mins".

Vinay Garhawal says:
2024-04-16 at 11:39 am

Please suggest a formula for below conditions up to 150 show result "3"
151 to 1200 show result "5"
1201 to 35000 show result "8"

Alexander Trifuntov (Ablebits Team) says:
2024-04-16 at 1:05 pm

Hi! We have a special tutorial on this. Please see: Nested IF in Excel – formula with multiple conditions.

Parth says:
2024-04-10 at 10:48 am

Hi,
I wanna create a formula within the IF function. I have got two variables in a list, and I want a mathematical formula to be applied based on the selection from the list.

Alexander Trifuntov (Ablebits Team) says:
2024-04-10 at 11:30 am

Hi! To perform calculations depending on the value of the variable selected in the list, use the recommendations in the article above and the easier examples in this manual: Excel nested IF statement - multiple conditions in a single formula.

Muhammad Essa says:
2024-04-10 at 5:51 am

I want create formula
if A1 is less then 100k then result willbe zero, if A1 greater than 100k & less then result willbe A1*2%
If A1 greater than 200k & less than 500k then result will A1*5%

Alexander Trifuntov (Ablebits Team) says:
2024-04-10 at 6:48 am

Hi! The answer to your question can be found in the article above in this section: Multiple nested IF statements. You can also find useful information in this article: Nested IF in Excel – formula with multiple conditions.

azhr says:
2024-04-04 at 7:08 am thank you so much sir, verry helpful TD says:
2024-04-02 at 5:05 pm

Trying to get a rolling calculation for 2 different factors.
To calculate data from column b2 to u2.
If a number is greater than 0, want the number 25 to be added for each cell and have total shown in cell v2. Purpose is to capture total fixed number of “items” looked at. What is entered is number of “items” broken.
The average is done in a different cell based off of total number of items looked at.

Alexander Trifuntov (Ablebits Team) says:
2024-04-03 at 7:43 am Hi! If I understand your task correctly, this guide may be helpful: Excel average without zeros. priya singh says:
2024-03-30 at 12:16 pm

I have 3 variable. In all 3 variable the value is present in only one variable so after making the 4 variable the data should come from there.

Alexander Trifuntov (Ablebits Team) says:
2024-04-01 at 9:02 am

Hi! To extract the value from the cell that contains data, you can use the following formula in a fourth cell (e.g., D1): =IF(A1<>"", A1, IF(B1<>"", B1, C1)) Read more: Nested IF in Excel – formula with multiple conditions.

adarsh singh says:
2024-03-29 at 12:17 pm

i am traying to write in if formula that a cell b7
if b7 is less than or equal to 50, value is 7
if b7 is greater than 50,but less than and equal to 150, value is 19
if b7 is greater than 150 but less than and equal to 400, value is 37
i want to apply this formula for a cell

Alexander Trifuntov (Ablebits Team) says:
2024-03-29 at 12:30 pm

Hi! You can find the answer in the first section of this article: Nested IF in Excel – formula with multiple conditions.

Vicky says:
2024-03-28 at 3:30 am

I am trying to insert IF where the following: If C2 = "Closed",(E2*M13) and if C2 = "PAI",(E2*M9) and if C2 = TRAY,(E2*M7) and so on. So if my c reads as closed, the cell calculates a value by taking the total in E and multiplying by % in M and if my c reads as PAI, it calculates the value by taking total in E and multiplying by corresponding % in M and so on.
I can't seem to add arguments after the first. I only have =IF(C2="closed",(CurrentWIP!E2*Sheet1!$B$18)) which works, looking only at 1 criteria. How do I add the same formula with different cells after the last ))

Alexander Trifuntov (Ablebits Team) says:
2024-03-28 at 8:31 am

Hi! You can find the examples and detailed instructions here: Nested IF in Excel – formula with multiple conditions. For example: =IF(C2="Closed",E2*M13,IF(C2="PAI",E2*M9,""))

Oomesh says:
2024-03-21 at 9:53 am

Hi, I have cell D88 with text "DCC Incentive|MASTERCARD DCC1080225681".
In cell E88 and F88 I have Debit & Credit amount respectively. For this transaction, the amount is in Credit amount column USD8. I want to put 2 condition in cell J88, 1st : if 1st 13 left character is "DCC Incentive" and if F88>0, the final result should be a "Deposit"or "Payment" if false.
can you help which if formula to apply here?

Alexander Trifuntov (Ablebits Team) says:
2024-03-21 at 10:48 am

Hi! If I understand your task correctly, the following formula should work for you: =IF(AND(LEFT(D88,13)="DCC Incentive", F88>0), "Deposit","Payment") Use the LEFT function to extract the characters from cell D88. Compare the result with the sample text. Depending on the conditions, use IF AND functions to write the final result.

colin macdougall says:
2024-03-14 at 12:44 am

I have a table with columns A through to Q.
I want a formula in column E that looks into B and if it finds "prof services" it always returns a 0, but if it doesn't it will multiple columns C x D. Thats all pretty simple but I also want other conditions to apply. If J states "No" then I want to multiple (C X D) x 0.8 unless Q contains "Partner". If "Partner" is returned I just want C X D.

colin macdougall says:
2024-03-14 at 5:05 am

The following formula is in Column E
=IFERROR(IF($B14="Prof Services",0,IF(Q14="Telstra",D14,IF(J14=”No”,$D14*0.8,D14)))," ") The output I am hoping to get:
If B14 = "Prof Services" then I want 0 in E14
But if B14 = anything other than "Prof Services" then I want the value of D14 in E14 unless J14 has "No". In that case I want the value of D14 multiplied by 0.8
But with one other condition, and that is if "Telstra" is in cell Q14, then just show D14

Alexander Trifuntov (Ablebits Team) says:
2024-03-14 at 8:03 am

Hi! The answer to your question can be found in article above and in this article: Nested IF in Excel – formula with multiple conditions. I can't offer you a formula as your description of the conditions is not very clear.

colin macdougall says:
2024-03-14 at 9:17 am Thanks Alex, Nested IF worked perfectly, once I worked out the logic. Thanks again calvin says:
2024-03-12 at 12:45 am

I'm trying to write a formula for this scenario: Cell A2
Cell B2
Cell C2 If Cell A2 is equal to zero, Cell A2 - Cell B2 = Cell C2
If Cell A2 is > zero, Cell A2 - Cell B2 = Cell C2 =IF(A2=0,A2-B2,IF(A2>0,A2-B2,"")) This will only shows result in Cell C2 once figure is key in. How do I make the same thing if Cell A2 is zero?

Alexander Trifuntov (Ablebits Team) says:
2024-03-12 at 9:19 am

I am not sure I fully understand what you mean. The answer to your question is already in your formula. I don't really understand why you need two conditions with the same result. One condition is enough. =IF(A2>=0,A2-B2,"")

Bill says:
2024-03-06 at 7:43 pm

I am having trouble combining a CONCATENATION formula with an IF statement.
This works as expected, both when A22 exists and when it doesn't: =IF(A22,"Hello","")
This works as expected: =CONCATENATE(TEXT(A22,"ddd. "),TEXT(C22,"mmm. dd"))
But this works as expected only when A22 exists. If A22 is empty I get "FALSE" not the " " I want: =IF(A22,CONCATENATE(TEXT(A23,"ddd. "),TEXT(C23,"mmm. dd")," "))
I suspect that the commas in the CONCATENATION statement are confusing the IF formula. But I've tried numerous additions of parentheses (to solate the CONCATENATION elements from IF) and Excel only balks.
Any thoughts?

Alexander Trifuntov (Ablebits Team) says:
2024-03-07 at 7:59 am

Hi! If I understand your task correctly, the following formula should work for you: =IF(A22,CONCATENATE(TEXT(A23,"ddd. "),TEXT(C23,"mmm. dd"))," ")

Bill Hunt says:
2024-03-07 at 5:23 pm

You're right. It does. Such a simple fix. All in the placement of the parentheses. I thought I had tried the very same placement at least twice before and gotten errors. But now it works. Thanks very much.

Janet says:
2024-03-01 at 2:28 pm

Excel wizards of this world, please help! I'm trying to write a VLOOKUP formula that will give me a "-" in return when the value is either not found or zero, and the actual value when it was found. I've tried several approaches, but I can't get it to work. My latest attempt was this one: =IF(OR(VLOOKUP(B1;location;5;FALSE)=0);(IFERROR(VLOOKUP(B1;location;5;FALSE))));"0";(VLOOKUP(B1;location;5;FALSE)) Thanks!

Alexander Trifuntov (Ablebits Team) says:
2024-03-04 at 7:06 am

Hi! To replace the #N/A error with a value and use the second condition, apply a nested IF. For example: =IF(ISNA(VLOOKUP(B1,C1:G15,5,FALSE)),"-", IF(VLOOKUP(B1,C1:G15,5,FALSE),"-", VLOOKUP(B1,C1:G15,5,FALSE))) Read more: Nested IF in Excel – formula with multiple conditions.

Lorenzo says:
2024-02-08 at 7:21 pm

Hi, I am trying to find a formula for the following logic. thank you in advance for helping out. If in Cell B1 there is one of 3 txt1, txt2, tx3, then it should return a value of -10 days prior to date from Cell A1. Thank you

Alexander Trifuntov (Ablebits Team) says:
2024-02-09 at 7:02 am

Hi! Have you tried the ways described in this blog post? It contains answers to your question. You could also find the solution in this article: Excel IF OR statement with formula examples. For example: =IF(OR(B1="txt1",B1="txt2"),A1-10,"")

Mei says:
2024-02-04 at 10:44 pm

I trying to work on If Column D = Y, Column A to apply formula figure 1.0x130% and if D=N column A should remain as 1.0

Alexander Trifuntov (Ablebits Team) says:
2024-02-05 at 12:58 pm

Hi! You can find the examples and detailed instructions here: Nested IF in Excel – formula with multiple conditions.

Ron says:
2024-02-01 at 10:16 pm

I am trying to reference a table on a different sheet in a workbook and populate a Yes or no in a cell if two criteria are true in that data set. Which Function do I use for that?

Alexander Trifuntov (Ablebits Team) says:
2024-02-02 at 7:51 am

Hi! Unfortunately, this information is not enough to give you any advice. If the information from the article above is not enough to solve your problem, I need to know more details about your task to help you.