Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 123. Total comments: 4573

  1. Hi Svetlana,

    Thank you for all of your support on this amazing website! I would appreciate your help making one If/then statement for the following:

    If GI = 8, then "-1"
    If GF = 8, then "1"
    If GF + GG = 8, then "2"
    If GH + GI = 1 or 2, then "3"
    If GH + GI = 3 or 4, then "4"
    If GH + GI > 4, then "5"

    Thank you!

    1. Hi Courtney,

      Here's the formula as per your conditions:
      =IF(GI1=8,-1, IF(GF1=8,1, IF(GF1+GG1=8,2, IF(OR(GH1+GI1=1,GH1+GI1=2),3, IF(OR(GH1+GI1=3,GH1+GI1=4),4, IF(GH1+GI1>4,5,""))))))

      Please keep in mind that nested IF formulas elevate the first condition first and if it's met, other conditions are not tested. For example, if GI1=8, the formula will always return -1 regardless of the values in other cells.

  2. Hi, so I'm trying to compare two values but they are not integers. How can I do this?

    Condition: If A1 is "High" and B2 is "Low", C1 should be "Q1". This would be different if A1 is Low and B2 is High, it will print "Q2" and so on. IS this possible?

    1. Hi Ivan,

      Of course this is possible with the following formula:

      =IF(AND(A1="high",B2="low"),"Q1",IF(AND(A1="low",B2="high"),"Q2",""))

  3. Good afternoon,
    I am working on a time sheet where I am trying to execute the following:
    (time out-time in)-meal break time. The output will go into the column "regular time". That is no problem and easily accomplished. The hitch is I want the out to go to the column "vacation time" if the block in the column "Vacation day" has a Y in in it. To summarize I am trying to get:
    "(Time out-Time in)-Meal break=regular time, if Vacation day="Y" then (Time out-Time in)-Meal break=Vacation time."
    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Raymond

  4. Svetlana,

    Hi! I have a spreadsheet that I am using to calculate a certain ratio. A lot of times it will come back with a null value which is what I want. Formula is: =IFERROR(AR416/H416,"")

    But, there are times where the value comes to $0.00 which unfortunately gets pulled into my AVERAGEIFS formula and pulls down the average considerably.

    So I tried this formula to counteract that:
    =IF(AR419/H419>0,IFERROR(AR419/H419,""),"")

    But now I get the divide by zero error in a few cells: #DIV/0!.

    Is there a way to use an IF statement to return a null value for everything that isn't greater than 0?

    Thanks so much!
    Shawn

  5. Perfect! Thanks so much for your help Svetlana!

  6. Hi WENDY,

    Try this one:

    =IF(AND(R1126>10.1,S1126>2.1),"TF2", IF(R1126>=10.1,"TF1", IF(S1126>=6.1, 3, IF(S1126>=2.1, 2,IF(S1126<=2,1)))))

  7. Hi! I am trying to add one more equation to my IF function and am not sure what to do. Here is my equation so far: =IF(R1126>=10.1,"TF1",IF(S1126>=6.1,"3",IF(S1126>=2.1,"2",IF(S1126<=2,"1")))) What I also need it to do is come back with a posting of TF2 if R1126 is greater than 10.1 AND S1126 is greater that 2.1. I cannot figure out what I am doing wrong. Any suggestions?

  8. Hi

    Can you help me with why this function is giving invalid errors? My error starts at this statement:IF(B43="",IF(A43=TODAY(),("OTW"),("CHECK ETA"))

    and again at this statement:IF(G43="",("IN ROUTE"),IF(L43"",("DELIVERED"),IF(H43="",("NOT AVAIL"),IF(I43"",("SCHEDULED"),("NEEDS SCHEDULED")))

    =IF(F43="",IF(A43>=TODAY(),("OTW"),IF(B43="",("CHECK ETA"),("ARRIVED")),IF(B43="",IF(A43=TODAY(),("OTW"),("CHECK ETA")),IF(D43="",IF(B43"",("AT PORT"),("OTW")),IF(E43="",IF(D43"",("INGATED"),IF(B43"",("AT THE PORT"),("OTW")),IF(G43="",("IN ROUTE"),IF(L43"",("DELIVERED"),IF(H43="",("NOT AVAIL"),IF(I43"",("SCHEDULED"),("NEEDS SCHEDULED")))

  9. Hi Svetlana

    How ican convert the currency value to USD if the available currency is anything else say , in 1 row (A:A) I have AED, Pounds, Euro, RS and they should be converted to USD in another ROW (B).
    1 more point is for the convenience in Row B we can see what currency has hadded. Like A1: 100 B1: Eur and in C1 : I have to have A1 value in USD here. We have standard conversion value.usd to Eur is 1.09 and for AED to USD is 3.67. so n so.

    Regards
    Rajesh

  10. I have several false and true statements across a row. I want to make one formula that says if all statements are false, give me false. If one is true, give me true.

    1. Hi Sarah,

      You can use a formula similar to this:
      =IF(COUNTIF(A1:E1, TRUE)>0, TRUE, FALSE)

  11. i have a query on the below data...

    i have max value and min sale value..
    i have value for march sale.
    if my sale is gone beyond max value upto 25%, then should be zero, or between the max value then it should be show some points based upon or will calculate in 10.
    The same is required for min value too...but i want to use both condition in one single column.

    Pls help.quite urgent for me.

  12. Hi, I am trying to do an IF function with two text variables. If the other cell says either SLEEVE or FLANGE, then the IF cell returns a 1. I can't figure it out for Excel 2013. Help! :)

    1. Hi Nick,

      What you need is a OR statement like this:

      =IF(OR(A1="SLEEVE", A1="FLANGE"), 1, "")

      1. And what do you add to the formula if it must leave the cell blank is nothing is entered in the column yet? I want the number 2 to be returned if any other word is typed in, but I need the cell to be blank if I did not type something in. Eg
        =IF(OR(A1="SLEEVE",A1="FLANGE"),1,2)

        1. Hi Vinette,

          In this case, nest another If function, like this:

          =IF(OR(A1="SLEEVE",A1="FLANGE"),1, IF(A1<>"",2,""))

  13. Hi

    My data is as below,

    If a cell says something else apart from shipped , then the other cell has to count the due days from today date to an already specified date (say acknowledged date).Which formula suits best for that.

    i.e. If S12 is not equal to "Shipped" then count Today-acknowledged (Q12). the result should be in days.

    Regards
    Rajesh

    1. Hi Rajesh,

      Try this formula:
      =IF(S12<>"shipped", TODAY()- Q12, "")

      1. Worked. GREAT!!! Thank you Svetlana.

        How ican convert the currency value to USD if the available currency is anything else say , in 1 row (A:A) I have AED, Pounds, Euro, RS and they should be converted to USD in another ROW (B).

        1. 1 more point is for the convenience in Row B we can see what currency has hadded. Like A1: 100 B1: Eur and in C1 : I have to have A1 value in USD here. We have standard conversion value.usd to Eur is 1.09 and for AED to USD is 3.67. so n so.

  14. I want to use if function for below details. Kindly assist:

    Slab Commission
    50000 200
    75000 500
    100000 700
    125000 900
    150000 1100

    I want to mark commission upon completing mentioned sales slabs, getting difficulty on doing so. your assistance required.

  15. Hi,
    I am looking to input an If function formula that gives me the sum of a range if the range does not contain text or a negative number, and if the range does contain either a text or a negative number, I need it to say Invalid.

    1. Hi Christian,

      You can use a formula similar to this:

      =IF(OR(SUMPRODUCT(--ISTEXT(A2:A6))>0,COUNTIF(A2:A6,"<0")>0),"invalid", SUM(A2:A6))

      1. Thanks, this worked out perfectly!

  16. Hi. I am new to excel so my query is probably straight forward. I have a list of numbers which when added together need to return zero if less than an amount, but the calculated number if it is more. I cannot figure out the second part of the calculation. =IF(D3+D7+V4<1801,0 what comes next to provide a sum more than 1801 when it is. Thank you

    1. Hi Ellie,

      You were almost there :)

      =IF(D3+D7+V4<1801, 0, D3+D7+V4)

      Just pay attention that the formula returns the sum of values in D3, D7 and V4, if it is equal to or greater than 1801.

  17. hi..i am trying to get the result for name whose res1 and res2 is not "N/A"
    Name res1 res2
    back 1 2
    shift N/A 1
    avast 2 1
    now 3 2
    book1 4 1
    apple 5 N/A
    back 2 N/A
    Avast 3 1
    Shift 7 N/A

    1. Hi Alex,

      If you are trying to count names whose res1 and res2 is not "N/A", use this formula:

      =COUNTIFS(B1:B6,"<>"&"n/a", C1:C6,"<>"&"n/a")

      If you are looking for something different, please clarify.

  18. I have a cell that I am importing Numbers or Words into.

    I attempted to use the following equation, which works but I was trying to get it to display an equation in the Number format.

    =IF(ISTEXT(M17), "Did Not Play", IF(ISNUMBER(M17), "Number", IF(ISBLANK(M17), "Did Not Play", "")))

    =IF(ISTEXT(M17), "Did Not Play", IF(ISNUMBER(M17), "=M17-36", IF(ISBLANK(M17), "Did Not Play", "")))

    The equation above does work, but it doesnt display the solution to the equation M17-36, it just displays the words.

    Any help is appreciated.

  19. Hi!

    I have written a formula as below.... it should give a result if I go & check manually in the data for an example, but it is showing as FALSE... please help....

    =(IF(Education_Experience_Matrix!G:G=Table134[[#This Row],[Column2]],Education_Experience_Matrix!F:F,IF(Education_Experience_Matrix!I:I=Table134[[#This Row],[Column2]],Education_Experience_Matrix!H:H,IF(Education_Experience_Matrix!K:K=Table134[[#This Row],[Column2]],Education_Experience_Matrix!J:J,IF(Education_Experience_Matrix!M:M=Table134[[#This Row],[Column2]],Education_Experience_Matrix!L:L,IF(Education_Experience_Matrix!O:O=Table134[[#This Row],[Column2]],Education_Experience_Matrix!N:N,IF(Education_Experience_Matrix!Q:Q=Table134[[#This Row],[Column2]],Education_Experience_Matrix!P:P,IF(Education_Experience_Matrix!S:S=Table134[[#This Row],[Column2]],Education_Experience_Matrix!R:R,IF(Education_Experience_Matrix!U:U=Table134[[#This Row],[Column2]],Education_Experience_Matrix!T:T,IF(Education_Experience_Matrix!W:W=Table134[[#This Row],[Column2]],Education_Experience_Matrix!V:V,IF(Education_Experience_Matrix!Y:Y=Table134[[#This Row],[Column2]],Education_Experience_Matrix!X:X,IF(Education_Experience_Matrix!AA:AA=Table134[[#This Row],[Column2]],Education_Experience_Matrix!Z:Z,IF(Education_Experience_Matrix!AC:AC=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AB:AB,IF(Education_Experience_Matrix!AE:AE=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AD:AD,IF(Education_Experience_Matrix!AG:AG=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AF:AF,IF(Education_Experience_Matrix!AI:AI=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AH:AH,IF(Education_Experience_Matrix!AK:AK=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AJ:AJ,IF(Education_Experience_Matrix!AM:AM=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AL:AL,IF(Education_Experience_Matrix!AO:AO=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AN:AN,IF(Education_Experience_Matrix!AQ:AQ=Table134[[#This Row],[Column2]],Education_Experience_Matrix!AP:AP))))))))))))))))))))

  20. Hi,

    I am trying to make a status condition. Below is are the cells that I want to have a status with. I have 2 columns, Case Status and Bug Status

    Sheet1
    cell# Case Status Bug Status
    G8 Passed
    G9 Failed Fixed
    G10 Failed Pending
    G11 New Bug
    G12 New Bug Fixed

    On the other (Sheet2) I want to know the general status of sheet1, if it is still "Open" or "Closed". My parameters would be, all "Failed" and "New Bug" case status should a corresponding "Fixed" status under Bug status column inorder to have a "Closed" status under sheet2 general status.

    Any help will greatly appreciated. Thank you very much.

    -Jade

    1. I've solved the problem using this statement:

      =IF(COUNTIFS(G8:G12,"=Failed",H8:H12,"Fixed")+COUNTIFS(G8:G12,"=New Bug",H8:H12,"Fixed"),"Open","Closed")

  21. I am working on the calculations of moon on excel,,I have download spreadsheets but they use macros,,,can you plz tell me that how can I extract real formulae from the codes???

  22. I need to find the exact data for using index and indirect formula

    A 50
    a 40
    B 30
    b 20

    Using Index and match funtion could you help me

    =MATCH(F$39,INDIRECT("'"&$A$1&"'!A"&F$41&":IV"&F$41&""),0)
    getting Error (#Name?)

    =INDEX(INDIRECT("'"&$A$1&"'!A"&$F$41&":Z"&$F$42&""),MATCH(F$39,TRUE,EXACT(INDIRECT("'"&$A$1&"'!"&F$41&":IV"&F$41&""),0)))
    Ref error

  23. It is exactly working in the same way I want.

    I am really sorry for too many basic questions...I am just trying to learn excel.

    with the above formula, I am getting the output in the way I want..but need a small extension to it.

    Say for example I have this formula in “AA” for SL (sick leave) and “AB” for VL (Vacation leave) “AC” for CF (Compoff)

    Now as per your formula when I give SL it is getting reduced but if I give VL it is not working.

    Also if I enter CF in the default 0 value it should increase the count, for example if enter CF anywhere from A1 to Z1 the count should get increased in “AC”

    1. Subbu,

      I am sorry, not sure that I can follow you. If you can post this question on our forum and attach a sample workbook for better understanding, our support team will try to help.

  24. =IF(OR(A1="A"), 20-COUNTA(A1:F1),"20") this worked well for a single cell.

    Now I want to use the same formula reflecting on multiple cells like:

    Targeted cells that I want the result is in from A1 to Z1 and I applied my formula on AA cell

    Now whenever I enter a value “A” in the cells anywhere from A1 – Z1 the count in the AA cell should get reduced.

    AA = contains my actual leaves per year
    A1-A20 contains the team member names
    A1 to Z1 cells contains the number of days in a month

    If anybody is on leave on that particular day, the count in the AA should get reduced.

    1. Got it :)

      In this case, you need a different formula, like this:

      =IF(COUNTIF(A1:Z1, "A")>0, 20-COUNTA(A1:F1), 20)

      The COUNTIF function counts the number of "A" in cells A1:Z1, and if it's greater than 0 (i.e. if there is at least one "A"), the count will be reduced.

  25. It is working for one cell that is A1. What if i want to apply the same formula from A1:Z1.

    I tried but it is giving an error

    please help

    1. Do you want to copy the formula to other cells? If so, you will need to use absolute or mixed cell references depending on your data structure. To be able to advise something more concrete, I need to know the exact formula you are using and what exactly result you expect it to return in other cells.

  26. sorry for coming back again...

    But in this formula no matter wheather i give "A" or "B" the count is getting reduced.

    =IF(OR(A1="A", A1="B"), 20-COUNTA(B1:F1), "A")

    i want the count to be reduced only if i give "A"...please help...

    1. Subbu,

      Yes, the formula reduces the count if A1 is filled with either"A" or "B" because that was the requirement: "if the empty cell is filled with “A” or “B” it should reduce the count".

      If you want the count to be reduced only if you input "A" in A1, then remove the OR statement from the logical test:

      =IF(A1="A", 20-COUNTA(B1:F1), "")

  27. It worked well thanks again

  28. That was a very speedy reply.. Your are great…appreciate it and thanks you very much for your help.

  29. it is a very small doubt..

    I am using this formula as below and want to add an “IF clause” to it saying that only if the empty cell is filled with “A” or “B” it should reduce the count...how can I get it...please help

    =20-COUNTA(C5:AF5)

    ignore the above post.....

    1. Assuming that the empty cell is A1, you can use the following formula:

      =IF(OR(A1="A", A1="B"), 20-COUNTA(C5:AF5), "")

      Instead of "" you can supply any value you want the formula to return if A1 is neither A nor B.

  30. it is a very small doubt..

    I am using this formula and want to add an IF to it saying that only if the empty cell is filled with A it should reduce the count...how can i do it...

    1. Hi Subbu,

      It could be something like this:

      =IF(A1="A", value_if_true, value_if_false)

  31. Hi

    Just wondering whether the following can be shortened
    i.e. if B58 shows either TX or SR it will generate
    6% in cell C58

    =IF(B58="TX",6%,IF(B58="SR",6%,))

    Thanks

    1. Hi!

      You can use the OR statement like this:

      =IF(OR(B58="TX", B58="SR"), 6%,)

      1. Thank you so much

  32. Hi

    Need an help in writing formula for attendance time tracking

    If the In Time is 09:00 and Out time is 17:30 - PRESENT
    If the In Time is 10:30 and Out time is 17:30 - HALF DAY
    If the In Time is 09:00 and Out Time is 15:00 - HALF DAY
    If the In Time is 09:10 - LATE
    If absent - ABSENT
    If Sick Leave - SL
    IF Comp Off- COFF

  33. I am trying to devise a spreadsheet to calculate wages using IF formula - can you help? If I enter house worked in column A. I want to calculate daily pay based on first 8 hours at one rate and anything above that at a higher rate. Thanks

  34. Can get help writing a formula that:

    if the number in cell F8 ends in .5 then G8 is $1.00
    if the number in cell F8 ends in .25 then G8 is $2.00
    if the number in cell f8 ends in 1 then G8 is 0
    if the cell f8 is left blank then G8 is 0

    This is an order form. For further clarification: if a customer orders a whole box (1) there is no charge. If they order a 1/2 box (.5) there is a split free of $1.00 for us to split the box. If they order a 1/4 box (.25), there is a $2.00 split fee. I need that cell to recognize that an order of 1.5 will need the $1.00 charge. Does that make sense? Help!

  35. Hi, I was wondering if you could help me on this.

    example:

    If A1 and B1 both equal 2, then say yes, elso say no.

    Thanks :)

    1. Nvm, I got it, I was reading your blog and found it, thanks :)

  36. I have also tried the following formula. But no matter if I get a number less than 60 I am still getting a "D" when i should be getting a "C","B","A".
    =IF(E20>100,"F",IF(E20<100,"D",IF(E20<80,"C",IF(E20<60,"B",IF(E20<40,"A")))))

    1. Hi Jessica,

      Because you put E20<100 before others "less than" conditions, Excel checks it first and if the condition is met it simply does not evaluate any other logical tests. And because any value that is less than 40 is definitely less than 100, your formula returns "D".

      As soon as you change the order of IF's, everything works as it should :)

      =IF(E20<40,"A",IF(E20<60,"B",IF(E20<80,"C",IF(E20<100,"D", "F"))))

  37. Please help :)
    I am trying to do multiple IF functions so i can get different grades according to a score. Can you please check my formula and tell me what am I doing wrong ? I have tried using the AND and the OR but it is not working. I think it could be the comas and parenthesis but i am not sure.

    Thank you,

    =IF(SUM(E11,E20)>100, "F",IF(OR(E20<80, "D"),IF(OR(E20<60,"C"),IF(OR(E20<40,"B"),IF( OR(E20<20, "A"))))))

  38. Hi

    I need to combine the following two IF Statements into one, I am thinking of using the OR function along it

    Function 1
    =IF(Q4="E",IF(T4>=12%,IF(T4<=19.99%,"0.5%",IF(T4<=24.99%,"1%",IF(T430%,"3%",0)))),"0%"))

    Function 2
    =IF(Q4="N", IF(T4>=10%,IF(T4<=19.99%,"1.25%",IF(T4<=24.99%,"1.75%",IF(T430%,"3.75%",0)))),"0%"))

    I tried using the IF OR function but got stuck.

    Any help would be appreciated combining the two functions into one

    1. Some help would be appreciated on this

  39. Please solve it

    I want to put up two conditions.

    Value - 1 :225
    Value - 2 :0.075%
    if ((225*0.075%=0.05,"Actual Value")).

    if the product of the both value is greater then 0.05 then i want the actual value means the product of the both value.
    Is it possible and if possible please explain.

    1. Hi Ankit,

      If my understanding of your task is correct, the following formula should work a treat:

      =IF(A1*B1>0.05, A1*B1, "")

      If the product of values in cells A1 and B1 is greater than 0.05, it returns that product, an empty string otherwise.

  40. Hi Svetlana,
    I need help with this formula:
    Quantity B1=500
    Frequency B2=4
    Cost B3=1000
    Shipment# B5="Shipment# 1"

    COGS B4=(B1*B3)/(12/B2) and then spread that B4 into the next 3 columns because (12/B2=3)

    The Quantity, Frequency, Cost will change so i need to write a formula for COGS B4 to capture this change. For example:
    Quantity B1=600
    Frequency B2=3
    Cost B3=500

    COGS B4=(B1*B3)/(12/B2) and then spread that B4 into the next 4 columns because (12/B2=4)

    I also need to write another formula to spread these info to the next columns. For example, if Frequency B2=3, then 4 months later in column F1=600, F2=3, F3=500,F5="Shipment# 2" and so on..

    Thank you for your help!

  41. Hi Svetlana, you're right the IFs are driving me crazy :s
    I can't seem to figure out what I'm doing wrong.

    I have a sheet that contains owner name, product name, number of products.
    I want to create a formula where I calculate the amount of products per owner.

    For example
    A: Pete - B: Apples - C: 40
    A: Steve - B: Lemons - C: 20
    A: Pete - B Apples - C: 30

    Pete is listed under the product "Apple" twice and in total he bought 70 apples. How do I calculate that number?

    I have tried the following:
    =IF((AND(A:A="Pete",B:B="Apples")),C:C)

    What I'm trying to say here is IF column A contains the name Pete AND column B contains the product Apples, THAN all the values in column C that contain the conditions from column A and B should be added up.

    I think I'm close with my formula but I can't seem to figure it out. Could you help me and let me know what I'm doing wrong?

    PS: sorry if I'm posting this multiple times but something seems to be going wrong when I press the send button.

    1. Hi Rachel,

      Excel has special functions SUMIF and SUMIFS to add up values based on one or several conditions, respectively. And they make things really easy :)

      Since you have 2 criteria, the SUMIFS function is the right choice:

      =SUMIFS(C2:C100, A2:A100, "pete", B2:B100, "apples")

      You can find the detailed explanation of the SUMIFS arguments in this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/

      PS No worries about the duplicate post, I've deleted it.

  42. Hey Svetlana,
    Can you please tell me if we can apply condition if formula in multiple cell at one time.

  43. THANKS

  44. Please advice the formula for the following condition
    1 ) If staff has taken leaves between 0 – 7 days will get 3 day paid leave rest all deduction
    2) If staff has taken leaves between 8 – 14 days will get 2 day paid leave rest all deduction
    3) If staff has taken leaves between 15 – 20 days will get 1 day paid leave rest all deduction
    4) more than 20 days 0 paid leave

  45. Hi Svetlana - i have read through all the posts as best i can. I'm sorry if you've answered this already:

    I have two cells (A1 and B1) that have numbers in them. Sometimes A1 is blank. When there are numbers in both, i want C1 to bring them together with a dash.

    For instance, if A1=3 and B1=4 then C1 should say "3-4".

    If A1=(blank) and B1=4 then C1 should say "4".

    Here is the forumula i'm trying to use:

    =If(A1>0,A1&'-'&B1,B1)

    However, excel does not recognize cell B1 in the formula.

    Thanks!

    1. Sorry - i was using the wrong "quotes" in the formula.

      1. Spot on :) Your formula with double quotes works perfectly!

  46. I am trying to produce a working formula for a sum-if-or statement and can't come up with something that works.
    I have numbers to sum in columns E11-E47. If Column G11-G47 shows a "T" or "S", I would like those numbers correlating in E11-E47 to sum, but if G11-G47 shows an "R" then the value in G11-G47 should result a 0.

    Make sense?

    I have =SUM(IF(OR(G11:G47="S",G11:G47="T"),E11:E47,0)) but it's not working properly and summing all of the values in E11-E47.

    Thanks!

    1. Hi Becky,

      Excel has special functions to conditionally sum cells, SUMIF and SUMIFS. In your case, you can use 2 SUMIF functions to sum values in cells E11:E47 if column G has either "T" or "S" in the same row. And then, add up the results returned by both SUMIF's:

      =SUMIF(G11:G47, "t", E11:E47) + SUMIF(G11:G47, "s", E11:E47)

      You can find more details and more formula examples in the following tutorials:

      SUMIF in Excel - formula examples to conditionally sum cells

      How to use Excel SUMIFS and SUMIF with multiple criteria

  47. IF(A1>=100, 100, IF(A1>49, A1, IF(A1>25, 100-A1, IF(AND(A1>0, A1<26), A1+50, ""))))

  48. Hello, I am trying to categorized a list of text values and created the following formula but it is not working, the search and if formulas can not be nested?

    =IF(SEARCH("License",$A11,1)>1,"License", IF(SEARCH("SnS",$A11,1)>1,"SnS", IF(SEARCH("Consulting",$A11,1)>1, "PSO", IF(SEARCH("Training",$A11,1)>1, "PSO","CHECK"))))

    Thanks

  49. I am trying to come up with a formula for the following,
    If H3, I3, M3 or N3>1,"Pass","Fails"
    this works for one cell or any array but not different cells. New to Excel formulas so any help would be appreciated. Thanks!

    1. Hi Michelle,

      Just use the following OR statement:

      =IF(OR(H3>1, I3>1, M3>1, N3>1),"Pass","Fails")

      1. When I write it like this it will show a pass for the column when all are above 1 but when the cell shows a 1 it will not prompt a Fail it only continues to prompt pass. What am I doing wrong?

        1. Will it matter if I have other formulas populated in the cells that are in this formula?

          1. Michelle,

            This should not really matter. If you can send your sample worksheet along with the expected results to our support team (support@ablebits.com) I think we will figure it out faster.

            1. Hi! Can u help me please..
              I would like to enter a value in any cell from a1 to d1 then it will display the same value in cell e1. What is the formula i should write? Thanks

            2. telle me formula

              Excel - If "pass","fail" - How do I set the formula

  50. hey, i'm trying to come up with a function to display Yes if value is more than 22.41 for female athlete OR if it is more than 22.47 for male athlete: if not it should display No.

    1. Hi Dee,

      Asumming that you have values in column A and male/female in column B, you can use the following formula:

      =IF(OR(AND(A2>22.41, B2="female"), AND(A2>22.47, B2="male")),"yes", "no")

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)