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.

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.

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")`

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.

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.

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.

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.

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.

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

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", "")))`

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:

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!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 2,934 responses to "Excel IF statement with multiple AND/OR conditions, nested IF formulas, and more"

I have an IF formula =IF(D8="X","1","") and now I want to add up the lines that have a 1 in them .. what type of formula will I need?

Hi Brad did you get any solution for that? I am also looking for the same.

Try =COUNTIF([Your Desired Range],"1")

Take the quote out:

=IF(D8="X",1,0)

Hi Svetlana..

In Excel Solver, my variable cell output should be either 0 or 20.

how to add this constraint please.

hi , i m trying to formula for this statment

if A1 greater then 20 twenty then add next row number..

A1 B2

25

32

20

5

I WANT IN B2 NOT MORE THEN 20. ADD NEXT ROW ABOVE 20

hi , i m trying to formula for this statment

if A1 greater then 20 twenty then add next row number..

A1 B2

25

32

20

5

I WANT IN B2 NOT MORE THEN 20. ADD NEXT ROW ABOVE 20

Hello,

I want to calculate an expiry date of a product. The products either have a 5year or 10year working life depending on whether its construction is "rubber" or "synthetic". Over three columns I have MATERIAL, DATED FITTED, EXPIRY DATE. If the material is rubber then I enter =DATE(YEAR(K6)+5,MONTH(K6),DAY(K6)) in the EXPIRY DATE. If the material is synthetic I enter =DATE(YEAR(K6)+10,MONTH(K6),DAY(K6)). Is there a way to make the EXPIRY DATE Column decide automatically how many years to add depending on the contents of the MATERIAL column?

I'm Really struggling with this.

Many thanks.

Tom

hello!

try using if formula for the solution like

=if(A2= "rubber", whole condition with 5 years expiry, if(A2="synthetic", whole condition with 10 years expiry,"")

I want multiple of this formula =IF(SUM(COUNTIF(A3,{"*ANCHORAGE*"})),"Alaska",IF(SUM(COUNTIF(A3,{"*Atlanta*"})),"Georgia"))

for several cities, but excel does not let me do more than two IF statements, is there another way to do multiple if statements in one formula?

You can nest as many IF statements as you'd like in Excel.

Just follow the formula:

=IF(Condition,True,IF(Condition,True,IF(...,False)...)

You're just replacing the False part of the IF statement with another IF statement every time. If the first condition isn't true, then it checks for the second, and then the third, etc.

please help in the below issue

i have count of 100 people name, and i have to add column B each person against 1 to 10 number.

So it will be like this

Mango 1

Mango 2

Mango 3

Mango 4............

please let me know the formula

its Cntr D i have used but i have large number of data so i can not use the same.

please suggest its urgent

Hi - I am trying to work out how to write a formula for the following:

If cellvalue1, "No"

If cellvalue is blank, ""

I have got the yes and no working by using this formula: =IF(E7>1,"No","Yes") but at the moment if there was nothing in cell E7, the cell I have the formula in shows "Yes" and I really want it to be blank (i.e. only say yes or no where there is an actual value in E7). Hope someone can help!

Thanks

kirstie

Hi Kristie, did you find any solution to this? I am in the same condition, looking for that function

Can you please help me to solve this

Fail+Pass+Pass = PASS

Pass+Fail+Pass = PASS

Pass+Pass+Fail = FAIL

Fail+Pass+Pass = FAIL

HOW ??????

Hi

Please help me..

My cell values are as follows:-

A1 B1 C1 D1

62 31 0

In cell D1 I need to use a formula that will calculate the value in A1 if C1 =0, but if

C1 >0, then I need to SUM B1+C1

hi.

try this

=IF(C10,C1+B1,0)

We want customers to receive a coupon based on the department they purchased from.

Coupon amounts by department:

Electronics—$25 coupon.

Toys—$20 coupon.

Sports—$15 coupon.

Shoes—$10 coupon.

Every other department—$5 coupon.

Create a column named “Coupon.”

Create a formula that displays the coupon amount for each customer based on department

Calculate the following score sheet

NAME GA TWI EWE FRAFRA PERCENT REMARK

a. Use data validate to allow NAME to have not more than 8 characters.

b. When a cell in the name section is selected, the user should be prompted not to type more

than 8 characters in the Name column.

c. Apply conditional formatting to display marks in red color and one decimal place if it is

smaller than 50.

d. Populate your table with the following data:

e. Calculate PERCENT (Note: Percent is the average score for a student) and REMARK.

REMARK is awarded as below:

i. EXCELLENT if any of the marks obtained is more than 90.

ii. V. GOOD if percent is greater than 60

iii. GOOD if percent is greater than 50

iv. FAIL if percent is below 50 or any of the mark is below 60

NAME GA TWI EWE FRAFRA PERCENT REMARK

SATOKA 40 59 24 100

ME LEE 60 67 80 100

EFO LEE 98 1 72 90

LEE SON 88 99 84 60

YOU LEE 76 100 100 90

Please , can someone help me with an appropriate function for question e

Cell A2-A10 has serial numbers

Cell B2-B10 has YES or NO

i want a formula for C2,

IF cell B2 is yes then i need the cell A2's value in cell C2

IF cell B3 is no then i need the cell C3 to be empty

I need to set formula for the target with criteria as check region is tier I or II then ach.vs target % check , ach.vs ach %, industry growth %, cagr% and stretch the % with 5 or 10 below matrix;

Ach Vs Target Fy 2020 E Ach Vs Ach Fy 2020 E Market share Fy 2020 E YoY Growth Fy 2020 E CAGR Target Growth over PY ach

Tier I Tier II Tier I Tier II Tier I Tier II Tier I Tier II Tier I Tier II Tier I Tier II

65% 65% 0% 0% 2% 2% -15% -15% -10% -10% 5% 5%

80% 80% 10% 10% 8% 8% 1% 1% 1% 1% 10% 10%

90% 90% 30% 30% 10% 10% 5% 5% 5% 5% 15% 15%

100% 100% 50% 50% 15% 15% 10% 10% 10% 10% 20% 25%

125% 125% 90% 90% 18% 18% 20% 20% 20% 20% 25% 30%

Hi Jessica, I am trying to use IF function e.g. If M3:M519 = JNB, then to add an amount that is in the corresponding column cell e.g if M58 = JNB then to add the amount in the corresponding cell e.g. B58 = 195

Does that make sense?

regards Tracy

Hi,

I have some data in which both column and row contains lot of data.

Just want to know that :

1.Column contains data in yellow colour.

2.Want to sum each row with yellow colour .

3.Total of yellow colour data pertains to different different user name.

4.Hence result should be total of yellow colour data in front of respective user name.

Kindly help on this.

Please give me a functio for a cell, as follows:

in cell b3 with only two value conditions b3=1 or b3=3

in order that:

than if b3=1 to give in cell c3 value effect 5

if b3=3 to give in cell c3 value effect 10

thank you.

Please give me a functio for a cell, as follows:

in cell b3 with only two value conditions b3=1 or b3=3

in order that:

if b3=1 to give in cell c3 the value effect 5

if b3=3 to give in cell c3 the value effect 10

thank you.

Please will you give me the function IF in a cell C3 depending of a value in cell B3 with two values , as follows:

in cell b3 with only two values conditions TRUE, with B3=1 or B3=3, in order that:

-for the TRUE two conditions: if B3=1 to give in cell C3 the value effect 5 , and

if B3=3 to give in cell C3 the value effect 10

-for the FALSE condition, if B3 has different value of 1 or 3, to give in cell C3 the value effect 0 (zero).

Thank you.

Hello Dim!

If I understand your task correctly, the following formula should work for you:

=IF(B3=1,5,IF(B3=3,10,0))

If there is anything else I can help you with, please let me know.

Hi,

if it is in excel workbook in one sheet >95 and in other sheet <95 written there, so how we can take average of that( with sign).

please help me to resolve this issue.

Regards,

Vivek

Hello!

If I understand your task correctly, the following formula should work for you:

=IF(OR (AND(Sheet1!A1<95,Sheet2!A1>95), AND(Sheet1!A1>95,Sheet2!A1<95)), AVERAGE(Sheet1!A1,Sheet2!A1),0)

I hope it’ll be helpful.

I'm trying to embed a MID statement for the answer if true. My forumula isn't working, however: =IF(B2=55,(mid(A2,4,2),B2).

Any ideas?

Hello, Annie!

Please try the following formula:

=IF(B2=55,MID(A2,4,2),B2)

How to calculate through xl formula dragging for 2000 rows

Logic.1 Sum first numeric cell starting from A2 at E i.e for row 1 is A2, row2 B2, row 3 C3 and row 4 B4

Logic.2 Sum from 2nd numeric cell to end

i.e for row 1 B1:D1 for row2 B2:D2 for Row3 C3:D3 and row4 B4:D4

- A B C D E F

1 1000 2000 3000 4000

2 - 500 600 700

3 - - 5000 6000

4 - 9000 7000 8000

Hi quick question

How can i do the following

Put conditional pricing based on the criteria..

If H2 is in special customer list (E2:E) than rate = D2 if not a special customer than rate is as listed in Pricing list according to gallon amount in cell J2

Thanks

https://docs.google.com/spreadsheets/d/1uk8MCtQ3ZdYR_KfJVWY4Svnx-Rmg11YuSePHxefn0jM/edit?usp=sharing

I have a problem where I want to generate a dynamic list based on 2 variables (an odd and a dollar amount) e.g.

Variable 1 - 1.2

Variable 2 - $1.00

I have the following function:

=IF(countif($E$1:E1,"No")=0,(if(($A$1*C1)>($A$2*4), ($A$1*C1)-($A$2*2), if(($A$1*C1)>($A$2*8), ($A$1*C1)-($A$2*4), if(($A$1*C1)>($A$2*16), ($A$1*C1)-($A$2*8),(if(countif($E$1:E1,"No")=1,(if(($A$1*C1)>($A$2*8), ($A$1*C1)-($A$2*4), if(($A$1*C1)>($A$2*16), ($A$1*C1)-($A$2*8)))),($A$1*C1))))))))

Cell C1 is the value of Variable 1 * Variable 2. Column E, is if a condition has been met, and an amount has been deducted from the outcome in the list.

Example of the list:

1 $1.20 $0.20 Yes

2 $1.44 $0.24 Yes

3 $1.73 $0.29 Yes

4 $2.07 $0.35 Yes

5 $2.49 $0.41 Yes

6 $2.99 $0.50 Yes

7 $3.58 $0.60 Yes

8 $2.30 -$1.28 No

9 FALSE -$2.30 No

I want #9 to be $2.30 * Variable 1, until it hits (Variable 2 * 8). Once it hits that limit, it will then be reduced by Variable 2 * 4.

There are quite a few more "limits" that I would like to set, but I've reduced them to simplify. Any help or suggestions would be great.

Thanks.

Hi..pls help me for the below conditions.

condition 1: wen entering D4 value =A, then E4 value should be=A

condition 2: wen entering D4 value =blank, then E4 value should be =blank

condition 3: if D4A and D4blank, means it has some data. so that time E4 should be "B".

Give me formula for above condition.

Thanks..

Hi Praveen,

If I understand your task correctly, the following formula should work for you:

=IF(D4="A", "A", IF(D4="", "","B"))

How to write the formula of if cell contains the range ±0.25 then return YES otherwise return NO in the Excel.

Hi Samy,

Assuming you are comparing cell B1 against A1, the following formula will return "yes" if B1 equals A1±0.25, otherwise "no":

=IF(AND(B1>=A1-0.25, B1<=A1+0.25), "yes", "no")

Dear.

In a range i have 10 customer name under this name i want to count the customer repeated ie duplicate as 1 and only one condition if cash customer more than one count it all ie 3 cash customer and 7 other customer total is 10 customers please help me to create one formula

Regards

Noby Paul

Dear.

In a range i have 10 customer name under this name i want to count the customer repeated ie duplicate as 1 and only one condition if cash customer more than one count it all ie 3 cash customer and 7 other customer total is 10 customers please help me to create one formula

Regards

Noby Paul

Thanks for your providing this is good lesson for me.

I have two products in kilograms with different Rates and if I sell my products more than first sell . I want continually minus .like

Qty Rate Amount Qty Rate Amount Sell qty sell rate Sell amount

12*15=180,. 10*17=170. Formula cell 15*....=....

what formula should I write here?

90-94, with honors

95-97, with high honors

98-100, with highest honors

Please help.

Hello!

Please use the following formula

=IF(A1 >= 90,IF(A1 <= 94,"Honor",IF(A1 <= 97, "High Honor", "Highest Honor")),"")

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hi Svetlana

I want to add two cells (G15+G9) and then use the result in another formula. However if one of the cells is empty I get a #value result. How can I get the result of either one of the cells is empty.

Hello Hino!

If cells G15 and G9 contains numbers, it is not quite clear why you get an error. I can suppose that there is a number in one of the cells and the other one contains a space or another non-printing character. Anyway, I recommend using the IFERROR function to process errors in formulas. You can find more info about this function here: https://www.ablebits.com/office-addins-blog/2017/09/27/excel-iferror-function-with-formula-examples/

Alternatively, you may find our Remove Characters tool useful for searching and deleting spaces and other invisible symbols. The tool is available as a part of our Ultimate Suite for Excel. Feel free to install the add-in in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

I hope it’ll be helpful.

Hi. Please help

I have 23,000 in income. I need to pay 0% in the first 2,000.

3% up to 5,000.

4% from 5,ooo to 10,000

5% on anything above 10,000

I need all the calculation in one formula.

Hello Marina!

Hello

If I understand your task correctly, the following formula should work for you:

=((A1-10000)*0.05)*(A1>10000) + ((A1-5000)*0.04)*(A1>5000)*(A1<10000) + (5000*0.04)*(A1>=10000) + (A1*0.03)*(A1<5000) + (5000*0.03)*(A1>=5000)

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hi Alexander. You are awesome. Thanksfor the help. I review and everything looks good except the last part of the formula. That should be 0% anything 2,000 or less and from 2,000 to 5000 is 3%. We are supper close.

650 ((A1-10000)*0.05)*(A1>10000) correct

200 ((A1-5000)*0.04)*(A1>5000)*(A1=10000) correct

150 (A1*0.03)*(A1=5000) review

In other words, we don't pay anything in the first 2,000 and we pay 3% from 2k up to 5K.

Thank you so much again

Hello Marina!

Please use the following formula

=((A1-10000)*0.05)*(A1>10000)+((A1-5000)*0.04)*(A1>5000)*(A1<10000)+(5000*0.04)*(A1>=10000)+((A1-2000)*0.03)*(A1<5000)*(A1>2000)+(3000*0.03)*(A1>=5000)

Thank You!

I used to make a formulae using the following scenarios;

> 1,000 amount would appear in the column to all who are non regular status

> 500 for non regular

example: If employee is on a non regular status the equal amount due for employee we will are 500.

Hello Amay!

For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

I have a spreadsheet with rows of data results for 5 tests that have been done and as such if the test has been done would expect to see either a PASS or a FAIL in each cell. However if one of test in that row is not done then that particular cell would be blank. Now I want to add a column which looks at each entry in the row and if any fails then it will remind the user to add some additional information, have tried using logic but I cannot seem to get it to ignore the cell if it is blank. Thus I am after something to solve the following

PASS, PASS, PASS, PASS, PASS = No Action required

PASS, " ", PASS, PASS, PASS = No Action required i.e ignore any blanks

PASS, PASS, FAIL, PASS, PASS = Add addition Info as it sees a FAIL

PASS, " ", FAIL, PASS, PASS = Add addition Info, again as it sees a FAIL

Anyway any help would be greatly appreciated as this is doing my head in...

Hello Andrew!

If I understand your task correctly, the following formula should work for you:

=IF(SUM(--(A1:E1="PASS"),--(A1:E1=""))=5, "No Action", "Action")

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hi Alexander,

Firstly thanks for the response, much appreciated. Now apologies, my fault as I should have probably mentioned that there were other cells between each of the entries which contains other information as such I need to look at each of these cells individually and then make a collective decision on the action, thus the spreadsheet breaks down as follows:

Cell 1 = Numeric data, Cell 2 = Time(hh:mm:ss), Cell 3 = Text, Cell 4 = Text & Cell 5 = Result (PASS/FAIL/"")

This then repeats itself another 4 times as each row has 5 probable tests, thus all I want to do is look at Cell 5 of each test and from those entries ascertain whether there is an action to be carried out for that row. i.e. if they are all PASS or blank then no Action is necessary, however if a FAIL is seen then ACTION is required.

Hope this makes sense and apologies for messing you about as I know you guys are likely busy.

Hello Andy!

Change the formula

=IF(SUM(--(E1="PASS"),--(E1=""), --(J1="PASS"),--(J1=""),--(O1="PASS"),--(O1=""), --(T1="PASS"),--(T1=""), --(Y1="PASS"),--(Y1=""))=5, "NO Action", "Action")

I hope it’ll be helpful.

I have a worksheet with over 10K lines of data which requires multi IF statements based on different criteria. I can't seem to write or find and IF statement that computes in a range. Example. If the value is above x and below y, multiply by Z. Can you help?

Thank you

Hello !

For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

hoping someone can help

im trying to sort my lab charges

if i have in column c I have "cbc" i want column e to have price for cbc which is $8.98

if in column c i have "bmp" then column e should show $14.56

etc

i have about 60 different labs and pricing. what is easiest way to do this so i dont have to manually enter the price each time

thanks!

Hello Aracely,

Please try a VLOOKUP formula. You can learn more about VLOOKUP in Excel in the above linked article on our blog.

Hope you’ll find this information helpful.

=IF($F$24,C6*0.75,IF($F$2>=8,C6*0)))

Hello and thank you ahead of time! Been working on finding a code for my spreadsheet for quite some time now, so I am very grateful for this site! Please help!

I have two sheets in a workbook. Sheet 1 contains data to be retrieved into Sheet 2.

Sheet 2!A1 is the "ID Number" to look up in Sheet 1 Column A, and Match or Lookup? if Sheet 1 Column D is "Yes", then return "Purple", if Column E is "Yes" then return "Red",if Column F is "Yes" then return "Blue",if Column G is "Yes" then return "Black"

The code I am working with and return value in:

Sheet 2 Cell D4

VLOOKUP(A1,Sheet 1!A:A,IF(Sheet 1!D:D="Yes","Purple),IF(Sheet 1!E:E="Yes","Red),"IF(Sheet 1!F:F="Yes","Blue),IF(Sheet 1!G:G="Black")))

Return value in Sheet 2 Cell D4 ---if D and F and G are all "Yes" , result should be Purple, Blue, Black

Hope I was able to explain what I am trying to achieve

I would like to know how to determine the effect of pest infestation on crop using the if statement. I am having trouble writing a formula. Can someone help me please.

Hello Michael!

For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

if 400 kg = 17.5

If 2000KG=22

Based on above assumption how to calculate the unit price for 375KG

what is the best formula for that ?

=IF((B5+C5)<=25400,"900"(B5+C5)<=29900,"1100"(B5+C5)<=30400,"1300"(B5+C5)<=33400,"1500"(B5+C5)<=35400,"1700",IF((B5+C5)<=37300,"1800",IF((B5+C5)<=41100,"2300",IF((B5+C5)<=44500,"2600",IF((B5+C5)<=50200,"2900",IF((B5+C5)<=51600,"3100",3200))))))

how can use these conditions in a formula excel doesnot allow morethan 8

I have been working on a formula for an movie list of 1500 Dvds, Blu-rays, and 4ks that I'm backing up on 3 HDDs, 1 for each type. Now all the movies have a "type" column C2 which will only be a value "SD", "SD/HD", "HD", "HD/UHD", "UHD". A "on HDD" column I2 which will only be a value "Y", "N", "Y/Y", "Y/N", "N/Y", "N/N". These represent if the movie was copied to hard drive/s (some have read errors), I have 3 more columns 1 for each drive L2 = drive 1 SD, M2 = drive 2 HD, N2 = drive 3 UHD. Now if I have a DVD/Blu-ray movie (SD/HD) the Y/Y part matches the type so if the DVD SD did not make it to hard drive but the blu-ray did it would be "N/Y" my ? Is if I just want a "X" in columns L,M,N if the movie made it to the coralating HDD =IF(AND(OR(C2="SD",AND(I2 ="Y",OR(C2="SD/HD",AND(I2="Y/Y",OR(I2="Y/N", "X","") I know I'm on the right track, I will do that for each drive column changing out "Type" but I need help with the correct way to write out the formula, Please Help, Thanks.

Hello Ryan!

If I understand your task correctly, the following formula should work for you:

=IF(OR(

AND(C2="SD",I2 ="Y"),

AND(

C2="SD/HD",OR(

I2="Y/Y",I2="Y/N",I2="X",I2="")

)

),TRUE,FALSE)

For me to be able to help you better, please describe your task in more detail. Your explanation of the formula is very difficult to understand. It’ll help me understand it better and find a solution for you. Thank you.

I need a formula that says if cell "M" is "true" and Cell "N" is "yes" then sum cells Q & T but if cell "M" is "false" and "N" is "no" the cell T but if cell "m" is "false" and cell "n" is "yes" cell T

=If(and(A2="A",B2="A" or "B"),"Yes","No")

Using like this if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify

=If(and(A2="A",B2="A" or "B"),"Yes","No")

Using like this in excel, if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify

Hello

Please try the following formula:

=IF(A2="A",IF(OR(B2="A",B2="B"), "Yes","No"),"No")

Hope you’ll find this information helpful.

1)If= first month(1 Jan to 31 Jan) all floors commission 2%

2)If= second month (1feb to 29 Feb) floor wise commission

Lower floor - 3%

Middle floor - 3.5%

Higher floor - 4 %

3) if = third month ( 1 March onwards) onwards floor wise commission

Lower floor - 2 %

Middle floor - 2.5%

Higher floor -3%

Please help how to create formula in 1 cell in excel

Hello Swapnil!

if you write data -

A B C D E

Month 1 2 3 2/10/2020

Lower floor 2 3 2 Middle floor

Middle floor 2 3.5 2.5

Higher floor 2 4 3

Please try the following formula:

=INDEX(B2:D4,MATCH($E$2,A2:A4,0), MONTH(E1))

I hope this will help, otherwise please do not hesitate to contact me anytime.

sorry I do not understand of this

A1 - Month, A2 - Lower floor, A3 - Middle floor, A4 - Higher floor, B1 - 1, B2 - 2, B3 -2, B4 - 2, C1 - 2, C2 - 3, C3 - 3.5, C4 -4 and so on. E1 - 2/10/2020 , E2 - Middle floor

Thank you very much it's working

but one more problem one Month Condition was changed. Condition is Date of period so formula not working...

*Date of period

1 ) 1 Jan 2019 to 2 Feb 2020 -commission all floors 2%

2 ) 3 Feb 2020 to 29 Feb 2020 - commission floor wise( L, M, H) 3 %, 3.5%,4%

3 ) 01 March 2020 to 31 march 2021* commission floor wise (L, M, H) 2%,2.5%,3%

Can you please help .....

Hello Swapnil!

Please try the following formula:

=IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(YEAR(TODAY()),3,31),3,0))))), "Date out of range")

Hope you’ll find this information helpful.

Formula not working on pending period 01 april 2020 to 31-03-2021 ...working only march 2020 ..

Third condition not completed.. Please help.

And one more condition add

3 types of sources in this

X, y, z

X and z eligible for all condition commission

But y not eligible for all. Only eligible 2% commission in all types of condition

Please help....

Please can someone help me with an excel formula?

3 sources of get commision

1)Comany

2) Customer ref

3) Direct sale

1) If multiple company commission for 2 %

2)if customer ref commision

*first ref commission rs 10k

*Second ref commission 20k

And

*Third ref commission 30 k

3) Direct commission "0%"

How to get commission formula in one cell

Hello!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you.

please help its very urgent

Hello!

If you want to learn something, try to understand how the formula works, and not ask for help all the time.

=IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(2021,3,31),3,0))))), "Date out of range")

If you write table

Column a : name of customer

Column b : source

Column c : source name

Column d : value

Column e : commission %(2%)

Column f : need formula this column

Source type 3

* broker

* Ref

* direct sale

Commission type

1)If broker sale 1 product get 2% commission ( value * 2%)

2 ) if any existing sale his ref through 1 product get amount benefit

* sale 1nd product deal get 10000 rs

*sale 2nd prduct deal get 20000 rs

* sale 3 rd product deal get 30000rs

And

3) if direct sale get 0% commission

Please help me how to merge in one cell (f column) formula

Hello!

Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

Hi there,

I have a total of "overtime and regular hours". I want my regular to show the hours in total cell of OV and RE, but not exceed over 40.

for example:

Total of overtime and regular is 32.

Regular cell show the 32.

but when the overtime and regular cell is > 40, the regular cell show only 40.

Can you please help me with a formula?

Hello Aimal!

If I understand your task correctly, the following formula should work for you:

in B21:

=IF(SUM(A1:A20,B1:B3)>40, 40, SUM(B1:B20))

where A1: A20 is overtime, B1: B20 is a regular time.

If that's not what you wanted, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

I want to put this equation in cell H57

1) if cell I54 value is = or than 20 and than 40, then multiply (I54 X N44)

so please advise how to right this equation

Hi,

Would the if function work for this?

Port/City Vancouver Montreal

Shanghai 27 34

Shenzhen 28 35

Guangzhou 35 42

Example:

Select: Shanghai, Vancouver

Time: 27 days

Select: Shanghai, Montreal

Time 34 days

Hello Jesse!

Please try the following formula:

=INDEX(B2:C4, MATCH("Shanghai",A2:A4,0), MATCH("Vancouver",B1:C1,0))

or

=INDEX(B2:C4, MATCH($E$2,A2:A4,0), MATCH($E$1,B1:C1,0))

I hope this will help, otherwise please do not hesitate to contact me anytime.

I am trying to create a formula that will calculate the LTV (loan to value) I can use. Here is what I have so far.

=IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%")))

However, I want to add another two IF conditions that will increase each percentage by 5% if they have 740+ credit AND $120000 annual salary. Thanks in advance for the assistance!

Hello Yixo!

If I understand your task correctly, maybe the following formula should work for you:

=IF(AND(D4>=740000,D5>=120000), IF(D3>=850000, "80%", IF(D3>=500001, "85%", IF(D3<=500000, "90%"))),IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%"))))

Hope you’ll find this information helpful.

hello, iam trying to counts pages based on if formaula, but i am not getting the answerer

If pages are 1 to 5 my answer should be 1

if more than 5 pages and less than or equal to 14 should be 2

If anything more than 14 pages my answer should be 3 - but this i am not getting

Here is my formula which i applied, can anyone suggest me on this

=IF(B25,"2",IF(B215,"3")))

Hello Ratheesha!

Please try the following formula:

=IF(B2>=1,(IF(B2<=5,1, (IF(B2<=14,2,3)))),0)

Hope you’ll find this information helpful.

Hi, I am looking for a formula to build where if the answer in cell E10 = Yes, then populate information in cell D10. As an example, if Yes, then it would populate YM-SL. The information in column D is various sizes. Or I could create a formula in column E, that if Yes, populate with answer from D. Either formula would be helpful. Thank you

Hello Robin!

I’m sorry but your task is not entirely clear to me. Do you want to fill in cell E or D? Maybe this is a formula =IF(E10="Yes",D10,"")? Could you please describe it in more detail? Thank you!

I am trying to get a formula as per condition below:

Condition 1 - 0<A6<=2

Condition 2 - 0<B6<=2

Condition 3 - N/A

"OK" if criteria as below met.

A6 B6

1 1

1 2

2 1

2 2

N/A 1

1 N/A

N/A N/A

"Not OK" if criteria as below met.

A6 B6

1 3

2 3

3 1

3 2

3 3

N/A 3

3 N/A

Need help. Thanks

I am trying to Populate a Date cell in L7 and I have two cells to choose from T7 and U7. I want L7 to be T7 if T7 has a date if not I want it to be U7 since that will always have a date. The Date in T7 will change due to plan optimization.

Hello Blaise!

If I understand your task correctly, maybe the following formula should work for you:

=IF(T7<>"",T7,U7)

If there is anything else I can help you with, please let me know.

how to connect two condition under each have some conditions?

Hello!

See above Example 1. Using IF & AND function in Excel

My apologies if you answered this already. I have a spreadsheet with Monday-Sunday in each column (A-G). State Names in Columns H-O. Unique People names in P column and their phone number in column Q.

Monday

Monday

Monday

Monday

Monday

Monday

I want to know who is available to travel on Monday to Utah.

I want the formula to provide me with a list of people and their phone number

Hello Sylvia!

I think that in your case the simplest and the most convenient way is to make use of Advanced Filter in your table instead of formulas. Please find advice on applying filters on our blog:

how to add filter, about advanced filter, highlight duplicates

Set a filter in the table and indicate criteria (what values you would like to see) in the necessary columns.

I have this formula =IF((AND($F2>=VLOOKUP($C2&""&F$1,Sheet3!$C$5:$E$1200,2,FALSE),$F2=VLOOKUP($C2&""&G$1,Sheet3!$C$5:$E$1200,2,FALSE),$G2=VLOOKUP($C2&""&H$1,Sheet3!$C$5:$E$1200,2,FALSE),$H2=VLOOKUP($C2&""&I$1,Sheet3!$C$5:$E$1200,2,FALSE),$I2=VLOOKUP($C2&""&J$1,Sheet3!$C$5:$E$1200,2,FALSE),$J2=VLOOKUP($C2&""&K$1,Sheet3!$C$5:$E$1200,2,FALSE),$K2=VLOOKUP($C2&""&L$1,Sheet3!$C$5:$E$1200,2,FALSE),$L2=VLOOKUP($C2&""&M$1,Sheet3!$C$5:$E$1200,2,FALSE),$M2=VLOOKUP($C2&""&N$1,Sheet3!$C$5:$E$1200,2,FALSE),$N2=VLOOKUP($C2&""&O$1,Sheet3!$C$5:$E$1200,2,FALSE),$O2=VLOOKUP($C2&""&P$1,Sheet3!$C$5:$E$1200,2,FALSE),$P2=VLOOKUP($C2&""&Q$1,Sheet3!$C$5:$E$1200,2,FALSE),$Q2=VLOOKUP($C2&""&R$1,Sheet3!$C$5:$E$1200,2,FALSE),$R2=VLOOKUP($C2&""&S$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2>=VLOOKUP($C2&""&T$1,Sheet3!$C$5:$E$1200,2,FALSE),$T2=VLOOKUP($C2&""&U$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2>=VLOOKUP($C2&""&V$1,Sheet3!$C$5:$E$1200,2,FALSE),$V2=VLOOKUP($C2&""&W$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2>=VLOOKUP($C2&""&X$1,Sheet3!$C$5:$E$1200,2,FALSE),$X2=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,2,FALSE),$Z2<=VLOOKUP($C2&""&Z$1,Sheet3!$C$5:$E$1200,3,FALSE))), "", 1)

It is working normally but it is very long, Is their any way to make it easier.

Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of L1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.

Thanks.

Hello Enity!

I’m sorry but your task is not entirely clear to me. What happens if B1 = "" and C1 = ""? Not all the conditions are clear. What formula are you trying to use? It’ll help me understand it better and find a solution for you. Thank you.

Hi I need to do a calculation total. I have a column called Quote/Won with one word in each row. I have a Labour £ column. I need to create a total of all labour revenue won. So if any figure in work labour column is won, the revenue figure shows in the total field. (single combined total field)

eg.

Total Labour revenue won £ 200

Won/Quoted Column---- Work Labour £ Column

Won £100

Quoted £150

Won £100

Hello Deb!

If I understand your task correctly, the following formula should work for you:

=SUMIF(A2:A7,"won",B2:B7)

You can learn more about SUMIF in Excel in this article on our blog.

Hope you’ll find this information helpful.

Hi all,

How to get different statement in one cell based on the different date ,

For example:

I have 5 document received date and 6 document sent date.

from that once document sent from my side i need statement automatically "Sent"

from my side document is pending statement should come " Pending"

and if i entered any date in completed column statement should come " completed"

Hello Jaybal!

I’m sorry but your task is not entirely clear to me. Excel cannot automatically indicate the status of a document. How does he know that you sent the document? It’ll help me understand it better and find a solution for you. Thank you.

I will receive document from vendor for review and once documents are okay i will approve that doc, if document rejected they will resubmit.

In my tracker Approved mean i will enter code "A"

if document reject i will enter code "C"...

These sequence will happen more than 7 to 8 times.

My requirement is Once i entered in C / A code i need result Approved /rejected in any one cell

Exampple:

(I need status automatically)

First time doc received A2= C Status "Rejected"

2nd time doc received C2= C Status "rejected"

3rd time doc received E2=A Status " Approved"

like this i have to check based on the code

Hello Jayabal!

If I understand your task correctly, the following formula should work for you:

=IF(COUNTIF(A2:E2,"A")>0,"Approved ","Rejected")

I hope this will help, otherwise please do not hesitate to contact me anytime.

I have two columns of data with the number set at 2 decimal places. column A for example in Cell A2 could read '10.2' and Cell B2 could read '11.3'. I want to be able to have cell C3 to state 'out of tolerance' if the range is outside of 10%. Is that possible?

Hello Shane!

You did not explain what it means "the range is outside of 10%". Maybe the following formula should work for you:

=IF((B2-A2)/A2>0.1,"out of tolerance","ok")

Expression (B2-A2)/A2>0.1 can be replaced with another.

I hope it’ll be helpful.

Thank you sir for your attention, if B1"", C1"", & D1"", it should give me the value of both K1, L1 & M1 & so on.

"Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of M1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.

Thanks.

Hello Enity!

Unfortunately, you did not give me detailed explanations. What formula did you use? Are you checking the condition in 15 cells at the same time? In which cell do you want to write the result? What should this result look like? How is the sum of the values of K1, L1 & M1 and so on? What are 15 arguments if there are only 10 columns between columns B and K? I can assume the following formula:

=IF(SUMPRODUCT(--(B1=""),--(C1=""), --(D1=""),--(E1=""),--(F1=""),--(G1=""), --(H1=""),--(I1=""),--(J1=""))=1, K1,B1)

But this is just a guess. And I'm not a telepath.

if AH1=Conus and D1=12, then 17

if AH1=Conus and D1<12, then 15

if AH1=Oconus, then 28

Hello Jessica!

If I understand your task correctly, please try the following formula:

=IF(AH1="Conus",IF(D1=12,17,IF(D1 < 12,15,"")),IF(AH1="Oconus",28,""))

Thank you so much!

ok, i need to throw another variable in - can this be done?

if AH1=Conus and D1=12, AND G1=9820580 or 159384 ....then 17

if AH1=Conus and D1=12, AND G1=6620363 ....then 15

if AH1=Conus and D1<12, then 15

if AH1=Oconus, then 28

Hello Jessica!

Please try the following formula:

=IF(AH1="Conus",IF(AND(D1=12, SUM(IF(G1={9820580,159384},1,0))=1),17, IF(OR(D1 < 12,AND(D1=12,G1=6620363)),15,"")), IF(AH1="Oconus",28,""))

I hope it’ll be helpful.

WOW. thank you so much

Hi

I am using a history database and would like to make it a little more efficient. Its purpose is to code surnames using four characters but when there are fewer than four characters, to fill in any gaps with a full stop; for example KIM would become KIM. If I have a column of surnames, is there a formula that would allow me to identify those with fewer than four code letters and which would add the full stop for me. The current process requires me to check each entry and for a large database it takes an age.

Hello Pail!

If I understand your task correctly, please try the following formula:

=IF(LEN(TRIM(A1)) < 4,"less than 4",A1)

hi,

my requirement is, I have 3 columns. If column 1 value meets my requirement and the columen 2 text is "x", extract column 2 text. Else no. could you please help how I can write formula?

Hello!

You can learn more about about functions IF and AND in Excel in this article on our blog.

Hope you’ll find this information helpful.

In same sheet read the 3 column V to x and match the condition of 25> cases in AC column and return the gift he qualified in AE column.

Hello!For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

I’m sorry but your task is not entirely clear to me.

I am very new to excel but I have a very detailed comparison I do manually on an excel sheet currently and I think you maybe able to help me.

I need to compare three columns and depending the message in those columns I need an out come.

Data:

A1 will contain update needed or blank

B2 will contain coordinator needed or blank

C2 will contain licensed or blank

Anytime column C2 equals Not Licensed no matter what any of the other columns say then I need column D2 to say Ineligible

If C2 is blank and b2 is coordinator needed and A1 is update needed the d2 needs to say coordinator and update needed

If c2 is blank and b2 is blank but A1 is update needed then I need D2 to say Eligible update

If all are blank then I need D2 to say No Action Needed

Thanks for any help !

Hello Diane!

If I understand your task correctly, please try the following formula:

=IF(C2="Not Licensed","Ineligible", IF(AND(C2="",B2="coordinator needed",A1="update needed"),"coordinator and update needed", IF(AND(C2="",B2="",A1="update needed"),"Eligible update", IF(AND(C2="",B2="",A1=""),"No Action Needed", "" ) ) ))

Hope this is what you need.

Hello, I need your help. I have a formula that is trying to figure out over and under numbers to meet goals.

=IF($B$4<$C$1,"")

actual B4=20 and C1=15(goal)

If the number on B4 is less than 15, I have it giving us a blank, which works fine. But I cant figure out how to add another IF formula that will tell it to give me the number we are over our goal by. In this case, total is 20, goal is 15, answer should be 5. So how do I add an if to my formula?

=IF($B$415, XXX)

Hello Maria!

If I understand your task correctly, please try the following formula:

=IF($B$4 < 15,"",$B$4-$C$1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

WOW! Thank you. Here I thought I needed a second IF statement. Thanks so much for your help. Maria.

Sir i want if ((1 to 2 = 2 , 2 to 4 = 3, 5 to 9 = 5 ))how to make that on if logic in single cell if any argument Parameter in between 1 to 2 must showing 2 , argument Parameter in between 3 to 5 = 3

Hello!

If I understand your task correctly, please try the following formula:

=IF(AND(A1 >= 1,A1 <= 2),2,IF(AND(A1 > 2,A1 <= 4),3,IF(AND(A1 >= 5,A1 <= 9),5,"")))

I hope it’ll be helpful.

Hi I need a formula to state this

In there will be Yes or No. If its Yes then use cell A1 if its No then use cell A2

Hello,

Please try the following formula:

=IF([condition],A1,A2)

You can learn more about Excel IF statement in this article on our blog.

Hope this is what you need.

=IF(O23="Inactive",0,IF(OR(L23=$L$3,L23=$L$4)=TRUE,$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))

The last if functions works when on its own, but when added to the previous functions it does not work. I do not see where the issue is: only adding one extra agruement to the formula to say if additional something is "Yes" then please add 150 to the amount.

Hello!

I'm sorry, I don't quite understand your case based on the details you provided. As for your formula, it should look like the one below:

=IF(O23=”Inactive”,0,IF(OR(L23=$L$3,L23=$L$4),$U$1*0%,IF(S23>0,IF(S23

I am trying to figure out how to return one value in a cell by evaluating 6 other cells. For instance, if cell C2 has a valid value, return C2. If "NA" I need it to look at D2 and do the same evaluation and return the valid value if not "NA". So, column c2 = CAR1, columns d2 through h2 have NA. I want column B2 to say CAR1. If column d2 = CAR2 and column c2, e2 through h2 have NA. I want column b2 to say CAR2. I am guessing I need a string but cannot figure it out. Thanks!

Hello Frank!

If I understand your task correctly, the following formula should work for you:

=INDEX(C1:H1,1,MATCH("NA",C1:H1,0)-1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

I am looking for help. My worksheet tracks work as well as issues. I need a formula for conditional formatting that will allow for a visual quick identification. This is what I think the formula should look like but I get an error. =IF(G22="Y","CORROSION",""(AND(H22="X","CLEANED"))); or =IF(G22="Y","CORROSION","",IF(G22="Y"(AND(H22="X"),"CLEANED"))

The first part of the Formula works it is trying to add the second half that causes the error. Anyone have a suggestion?

Hello Jesse!

Your formula contains errors. 1. You cannot use the = sign inside a formula. 2. The conditions AND and OR are incorrectly described. You have not explained how the formula should work, so I can’t fix it. To do it yourself, read the instructions Excel IF statement with multiple AND/OR conditions, nested IF formulas.

I hope this will help, otherwise please do not hesitate to contact me anytime.

On my spread sheet I have criteria that represent if an item is corroded or not, block 1 "item", block 2 is "inspected", block 3 "corroded", block 4 'cleaned/primed", and block 5 "notes". If it is corroded I put a "Y" in the corroded block. In my note block I have a formula, =IF(G4="Y","CORROSION",""), if this block has a "Y" it will show the word "CORROSION" this works perfectly. What I need to do is add to the formula that considers the corroded block with a "Y" and the clean primer block with a "X" to return with the word "REPAIRED". Is there a way to do this? I need this as a quick reference so at a visual glance the item can be easily identified as repaired.

Hello Jesse!

If I understand your task correctly, the following formula should work for you:

=IF(G4="Y", IF(H4="X","Repaired","CORROSION"),"")

I hope this will help, otherwise please do not hesitate to contact me anytime.

Thank you sir works perfectly.

Please help me correct these formulas

=(F32); IF(F16:F21="0",) - H47 = F32 but if cells F16:F21 = 0 then H47 Should = 0%

Also I require a formular If C11="good",15; IF C11="Satisfactory",9; IF C11="Poor",0; IF C11="N/A",N/A)

Thank You

Hello Mona!

I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. What you wrote is not a formula.

This formula may be suitable for you, but your explanations are very inaccurate.

=IF(C11="good",15, IF(C11="Satisfactory",9, IF(C11="Poor",0, IF(C11="N/A",#N/A, "" ) ) ) )

Hi

Yes, I figured i messed that up really bad and managed to correct it to this

=IF(C11="Good";15;IF(C11="Satisfactory";9;IF(C11="Poor";0;"N/A"))) which works well,

the only problem is i dont know how to go about having a formula that ignores a N/A and still work out at 100%. I tried yours now and this just leaves the score as a positive.

Hello Mona!

Excel uses the IFERROR function to handle errors. Read more here https://www.ablebits.com/office-addins-blog/2017/09/27/excel-iferror-function-with-formula-examples/

If column A is the equally weighted value of a score and column B is the score (0-5 or N/A), and a score of N/A is entered on a row, I want the weight to be removed from the row and equally redistributed across all scored rows, thereby increasing the weights in column A.

Is this possible?

Said another way, if all rows had a numeric score, then all weights would be 3%, for example. However, if there were several rows that got an N/A, for each N/A, the 3% value of the weight would be equally redistributed across column A to show what the final, equally weighted values would be.

Thanks for your help! I hope this question makes sense!

Hello Lisa!

If N/A is a text, not an error value #N/A, then use the following formula to calculate the weight:

= IFERROR (B1/SUM($B$1:$B$50), 0)

I hope this will help, otherwise please do not hesitate to contact me anytime.

I need a formula that looks at a date range on 1 sheet but returns the value on a different sheet, looking at values on the original sheet but only IF a different column on the original sheet shows certain TEXT.

I'm doing the formula on a sheet named 'April!'

Data is on 'Ian!' sheet

So column B6:B500 on Ian! will have the date

Column J6:J500 has the income but it is dependant on the value in column C whether it shows as 'New' or 'Renewal'

Please help, thank you

I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!

=SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))

Hello Rachel!

I’m sorry but your task is not entirely clear to me. I need more details to help you. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Write down what specific criteria must be met so that the value of sheet 2 is written to sheet1. Thank you.

Hi All,

I need an excel formula to TEXTJOIN of multipel cells with a matching condition of two column vaues. For example.

Column 1 has Managername Manager1, Manager2, Manager3, Manager4, Manager5

Column 2 has Employees EMP1, EMP2, EMP3, EMP4,EMP5, EMP6 etc

column 3 has OnBench, InProject,InProject,InProject,OnBench

I need to list this into other sheet of the same workbook, with the below condition.

List of all employees, with join text separated by comma into single cell against that Manager name who are OnBench.

Can anybody help with this?

Thanks a lot in advance.

Hello Surya!

I recommend using function Vlookup for multiple values/ Read more here.

I hope this will help, otherwise please do not hesitate to contact me anytime.

Ok so I'm trying to compile a file that shows if a particular person has been called during the week.

Each Day has a sheet that populates a persons detail from a unique identifier (Tech Id) and can be marked as YES or NO as to whether they have been called that day.

Tech ID, Name, Called?

I then have a weekly review sheet that i need to populate. same column lay out

So if the Tech ID in A2=FS999 and the corresponding tech ID = YES on any of the daily sheets then it should fill in on the weekly tracker. I Just cant for the life of me get my head around the complexity of so many IF's and OR's .

Can Anyone Advise?

Hello Mike!

If I understand your task correctly, the following formula should work for you:

1. To calculate data for several conditions on several sheets, you can use the formula

=SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"),"FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes"))

2. Use this formula in your condition

=IF(SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"), "FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes")) > 0,"Yes","No")

I hope this will help, otherwise please do not hesitate to contact me anytime.

Kindly help me to get percentage in multiple amount different percentage ratio

For example:

60000 to 100000 = 5.5%

100001 to 150000 = 8%

150001 to 200000 = 12%

200001 and above 15%

How to calculate if i have many column in different amount and need to get percentage as per above how to use formula, kindly help urgently. Appreciate any can help me in this regards urgently.

Thank you

Pravin Rupapara

Hello Pravin!

You may use the FREQUENCY function to calculate the number of values in the particular range:

=FREQUENCY(B2:B100,F2:F5)

Where B2:B100 - your data range and F2:F5 - the cells that contain your lower bounds (i.e. 60000, 100000, 150000, 200000)

Select the range of empty cells (G2:G6, for instance) that has one cell more than F2:F5. Paste =FREQUENCY(B2:B100,F2:F5) in the formula bar and apply it as an array function by pressing Ctrl+Shift+Enter. Then divide every resulting numbers by the total of values (the formula for the last one would be =COUNTA(B2:B100)

Enter these formulas into H2:H6 and you'll get the percentage you need.

I want the reason for result of IF formula. That if result came was PASS then why? Is it due to column 2 or 3.

Hello SHRIKRISHNA!

To control how the formula is executed, you can use the Evaluate Formula tool. It is located in the menu on the Formula tab.

I hope this will help, otherwise please do not hesitate to contact me anytime.

Thanks sir, evaluate formula shows it but I want that this evaluation in column next to result of formula.

Formula is= if(and(T3>3,U3>=9,v3<2,w3="NO",X<25),"eligible","ineligible")

If result come ineligible then I want to know that due to which column this result came.

Please guide

Regards

Formula is =IF(OR(AND(G16="D",Q16>300000),AND(G16="V",Q16>300000),AND(G16="I",Q16>1000000),AND(G16="ORC",$L$5>600000)),"Y","")

It's returning the first two conditions "D" & "V" correctly but not the last two conditions. What am I missing? Please help. Thank you.

Hello Luisa!

I copied the formula from a blog in Excel and made sure that it works. Write in detail what results you want to get. For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

Thank you for your reply.

What I'm trying to accomplish has several conditions, so if:

Service column has "D" and total of more than 300K the result should say "Y" for yes.

OR

Service column has "I" and total of more than 300K the result should say "Y" for yes.

OR

Service column has V and total of more than 1M the result should say "Y" for yes.

OR

"H" total column is more than 600K the result should say "Y" for yes.

I hope this is clear explanation. Thank you so much for your help.

Hello Luisa!

Based on your explanations, the formula needed to make very small changes

=IF(OR(AND(G16="D",Q16 > 300000),AND(G16="I",Q16 > 300000),AND(G16="V",Q16 > 1000000),AND(G16="H",Q16 > 600000)),"Y","")

Hope this is what you need.

Gud day Sir, please I have a large document that is up to 20 pages & maybe more. I want the heading which is in row 1,2,3,4,5 to show at the top of each page, I used the print title option and it worked perfectly. Now I want the bottom note which is up to eight rows to show at the bottom of each page. How can I make that work?

Please I need help on that.

Thanks.

How do I extend this formula to retrun "Agency" if employer column does not = J Smith Ltd.

Employer Source Fomula

J Smith Ltd Direct =IF(C4="J Smith LTD","Direct")

ABC Recruit Agency

XYZ Recruit Agency

Hello Tim!

If I understand your task correctly, the following formula should work for you:

=IF(ISERROR(SEARCH("J Smith LTD",C4)),"Agency","Direct")

Hope this is what you need.

WHAT SHOULD BE FORMULAE CONSIDERING BELOW CONDITIONS:

- Cell is greater than equal to 5 = P-HOME

- Cell is less than 5 = HLWP

- Cell is "0" OR "NULL" OR "#N/A" = LWP

Hello!

If I understand your task correctly, the following formula should work for you:

=IFERROR(IF(B1 >= 5,"P-HOME", IF(B1=0,"LVP","HLWP") ),"LWP")

Hope this is what you need.

HI,

Please suggest how automate in sheet B

Sheet A.

01-05-2020 02-05-2020 03-05-2020

Branch Cash Transfer Cash Transfer Cash Transfer

A 200 90 500 152 5165 2132

B 500 100 700 515 6516 98

C 700 930 785 515 212 19

Sheet B.

Date 02-05-2020

Branch Cash Transfer

A ? ?

B ? ?

C ? ?

Hello Anoop!

I’m sorry but your task is not entirely clear to me.

For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

I am trying to work out an IF formula for the following. I have a spreadsheet that details stock and whether it is in date, out of date or expiring soon. I need the cell to show whether an item expires one month from today, is in date or out of date. I am struggling to work out the IF formula - this is what I have come up with so far, but am unable to get any further. Any help would be greatly appreciated by this newbee to Excel

=IF(E3<TODAY()*AND(-365-335),"Exp 1 mth",IF(E3<TODAY()-365,"Out of date","In date"))

Hello Sarah!

If I understand your task correctly, the following formula should work for you:

=IF(E3>TODAY(),"In date",IF(E3 > EDATE(E3,1),"Exp 1 mth","Out of date"))

I hope this will help, otherwise please do not hesitate to contact me anytime.

if the 1st row is greater than the other row then the answer is 1

if the 1st row is lesser than the other row then the answer is 0

if the 1st row is 0 than the other row is 0 then the answer is 1

if the 1st row is equivalent to the other row then the answer is 0

Hello Vincent!

I’m sorry but your task is not entirely clear to me. Do you want to compare rows or cells? What does "row is greater than the other row" mean? Could you please describe it in more detail? Thank you!

Hello,

I need the following formula:

if in column A I have the word "Revision", I need to put it in column B but not at same level (-1 level).

Exemple: if A4=Revision: 2.1--> put all content in B3

Thank you very much

Hello Pascale!

If I understand your task correctly, the following formula should work for you:

=IF(SEARCH("Revision",A4,1)>0,A4,"")

If there is anything else I can help you with, please let me know.

Hi,

I order supplies for my company and have created a sheet that has 13 tabs, one for each 4 week period and a Summary tab at the end. On each tab I have:

ItemOrdered Qty Item# Supplier Cost$ DateOrdered DateReceived QTY/CS

I need to create a formula where IF a certain item is ordered (say triggered by Item#), THEN, the value in the Qty column is tallied as a sum on my Summary tab. Does that make sense?

Thanks in advance!

Matt

Hi,

Great article!

In my example below, is it possible to add to the formula so that results in column 'C' would also take into consideration the value in column 'B' and (subtract 10 if 'B'=1) or (subtract 20 if 'B'=2)

Hope this makes sense, thank you!

A B C

1 35 1 150

2 33 150

3 40 2 200

4 41 200

=ifs(and(A1>=30,A1=38,A1<42),200)

Hello!

Your formula does not work. I did not understand what result you want to get. If there are already numbers in column C, then they cannot be changed by the Excel formula. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

thanks for the response.

I didn't realize I pasted wrong the formula

here is the correct one I use:

=ifs(and(A1>=30,A1=38,A1=" and "<") from Column 'A' which are size values. Now I want to add additional criteria (Column 'B') which is a quality grade (A,B,C or can be switched to numbers 1,2,3) so the calculated value (price) in column 'C' would be less by 10 if 'B=1', less by 20 if 'B=2', less by 30 if 'B=3', if 'B=blank' leave it as it is.

Hope this makes sense and thank you for all your help!

Alex

...here it is again, for some reason when I publish the comment it changes the formula pasted.

I'll try again with no "=" sign

ifs(and(A1>=30,A1=38,A1<42),200)

ifs(and(A1>=30,A1=38,A1<42),200)

...Never mind, the forum messages will change the formula every time I submit it.

ifs(and(A1>=30,A1<38)=38,A1<42),200)

I'll put it in words:

if "A1" is "greater than or equal to" 30 and "less" than 38, than 150, if "A1" is "greater than or equal to" 38 and "less" than 42, than 200

hope this makes sense as it won't let me paste the formula in the chat correctly.

Hello Alex!

If I understand your task correctly, the following formula should work for you:

=IF(B1<>"", (IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,""))) - (B1*10), IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,"")))

I hope this will help, otherwise please do not hesitate to contact me anytime.

Yes! That's it!

Thank you sooooo much!

Appreciate all your help!

Hello, how do write a formula for this? If total is between .01%-.99%, output should be $40, if between 1% and 1.99% - output should be $210. I tried IF and also IF(AND with , but I can't get it to work...help please. Thank you so much!

0.01% ---> 0.99% $40.00

1% ---> 1.99% 210.00

2% ---> 2.99% 240.00

3% ---> 3.99% 270.00

4% ---> 4.99% 300.00

5% ---> 5.99% 330.00

6% ---> 6.99% 370.00

7% ---> 7.99% 410.00

8% ---> 8.99% 450.00

9% ---> 9.99% 490.00

10% ---> 10.99% 540.00

11% ---> 11.99% 590.00

12% ---> 12.99% 640.00

13% ---> 13.99% 690.00

14% ---> 14.99% 740.00

hello Phatima!

Write your values in columns A, B, C. A1 - 0.01% B1 - 0.99% C1 - 40.00, etc. If the final value is written in F1, then the formula for determining the output will be as follows:

=VLOOKUP(F1,A1:C15,3,1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hello,

I'm hoping to add a calculated field to my pivot table and need a little help.

A B C calc field

1 "A only"

1 1 "A & B"

1 "C only"

1 "B only"

1 1 1 "All 3"

1 1 "B & C"

1 1 "A & C"

I'm stuck, wonder if someone can help me:

I need ranges-

if cell value is =1500 and =3000 and =5000 "5000+"

I know it's where I'm putting the () but I can't seem to get it right?

Hello Cindy!

I’m sorry but your task is not entirely clear to me. I hope you have studied the recommendations in the above tutorial. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.

sorry, it didn't type out correctly

If h2 is less than 1500, then "<1500", if h2 is greater than or equal to 1500 AND less than 3000, then "1500-2999", if h2 is greather than or equal to 3000 AND less than 5000, then "3000-4999", if h2 is greater than 5000, then "5000+"

Hello Cindy!

Please try the following formula:

=IF(D2 < 1500,"<1500", IF(D2 < 3000,"1500-2999",IF(D2 < 5000,"3000-4999", "5000+" ) ) )

The second way:

Write your values in columns A, B, C. A1 - 0 B1 - 1499 C1 - <1500 A2 - 1500 B2 - 2999 C2 - 1500-2999, etc. If the final value is written in D1, then the formula for determining the output will be as follows:

= VLOOKUP (D1, A1: C4,3,1)

I hope this will help, otherwise please do not hesitate to contact me anytime.

Hi,

I'm trying to write an "If" statement if 3 variables in different cells match up with each other then "Y" else "N"

If [A2]=[C2]=[D2]THEN "Y" ELSE "N" ENDIF

Hello Michelle!

Please try the following formula

=IF(A2=B2,IF(B2=C2,"Y","N"),"N")

I hope it’ll be helpful.

Hello,

Just want to say thank you , you doing great job, lord shower grace on you.

I was writing my problen but suddenly answer clicked, thank you again.

Thank you so much!

I am trying to compare a sales amount in a cell to a commission schedule and based on teh sales figure it will bring multiply the sales times the correct commission %. I know this is not correct but I am not sure where I am going wrong

=IFOR((AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)

updated but still not correct

=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)

=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)

Hello Keith!

Please describe your problem in more detail. The IFOR function does not exist. What does the B25A16 mean? It’ll help me understand it better and find a solution for you. Thank you.