ifs

  1. N0t Y0urs

    Sim if with conditions

    Hi I want a formula that will sum data in a range based on a set criteria and I’m struggling. I have 1 - 100 data sets that I want to total 3 different columns with. Currently my formula reads = sumif(acc!$d$4:$ec$4,’FWD’,acc!$d5:$ec5) and that’s working to give me the total for all 100 data...
  2. S

    IFS Formula with multiple conditions. Error: function in the formula causes the result to change each time the spreadsheet is calculated

    I need big-time help here! I have altered the formula so many times and have gotten completely confused and still, get an error "The function in the formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but the...
  3. S

    Index match with multiple criteria in a table

    Hi, Please see the picture attached. For every number in the left column I am trying to match that number and the property type found in the row next to it and return the associated number at the top. So for example for 103 0 06 with property type house-Y i want it to return 0. Note that I want...
  4. T

    Check if row values in column cannot be found in another range

    Hi all, I would like to: Check if row values in Column AP cannot be found in another workbook's Range("D2:D31"), return "NULL" in Column AO Else, return row values in corresponding row of Column AN in Column AO I'm not sure how to create a formula which would find row values which do not...
  5. T

    If row values found in another range, return a specific value, else leave original value intact

    Hi all, I would like to: Check if row value in Column AN can be found in another workbook's Range("D2:D30") If true, then return "APAC" in Column AM. If false, do nothing and leave row value as it is. However, my formula entered below is returning 1s and FALSE: Range("AM2")...
  6. T

    If Multiple Condition and Nested CountIf Formula Not Working

    Hi all, I would like to: Check if row value in Column AM = "APAC" Check if row value in Column AR = "APAC" Check if row value in Column AJ has partial string match containing "OFIA" Check if row value in Column AJ has partial string match containing "Intra-Asia" If all above...
  7. J

    Excel Calculation Performance: IFS vs OR

    Hello, I read that an OR function will evaluate ALL logical expressions before returning a result, which seems wasteful because you'd think that as soon as it evaluated a single TRUE expression, it should no longer need to evaluate the remaining expressions. Does the IFS function also evaluate...
  8. C

    If statement to give a 'yes' or 'no' if a time fall between a range

    Hi all, I am trying to get a yes/no answer if a cell is between a time range. It is set up as a table on Office 365. I have tried IF and IFS statements with both AND and TIMEVALUE in it. I have attached a screen grab of what i'm working with at the moment. Any help is greatly appreciated. Chris
  9. J

    Calculate Number of Days Overdue using either column, with one taking precedence over the other.

    Hello, my 2nd post here and I'm pulling hair on another stuck Excel sheet. I've got an Excel sheet prepared however just couldn't upload it here. Actually just one question however with several conditions so I'm putting them into bullet points numbered below: 1. For each document, how do I...
  10. L

    Using an array function gives different results than cross-referenced function

    Hello all! New User here, so bear with me I'm working on a little pet project, attempting to parse out data from a large date range using smaller date ranges. The goal is to automatically get references given a date range that can be used for math in other cells. Most of this can be done with...
  11. P

    Conditional formation in planing calendar

    Hi Forum, I'm reach out to you to ask if someone can help suggest improvement to a function for conditional formatting. I created a calendar where I can plan resources by entering the number of days and the following cells are colored by using conditional formatting. This far I have only...
  12. A

    Quartile formula Multiple IFs across multiple worksheets

    Hi all, I was hoping to get some assistance with a formula I have. I am trying to calculate the second quartile (using multiple if statements) for a specific column across 2 worksheets. Alternatively, I could copy and paste the data into the one worksheet, but I am trying to avoid this. This...
  13. B

    Average of 1 cell depending on dependent of another cell

    Hey, I'm trying to find the average of 1 cell that is linked to another cell. In the image below, I am trying to get the average of the numbers linked to column A - so AAA and BBB. Taking 'AAA' as an example, I first need to find AAA in column B and then take the letters in column C and find...
  14. N

    Date Result returns a "Expired" or "Expiring" or "Current"

    I'm sure I'm just missing something in my statement because i get a formula error, but I'm trying to have my Column "i" look at Column "E" which is a date format. Column "i" would return "Expired" if the date is <today(), "Expiring" if the date is between today and 120 days, and "Current" if its...
  15. M

    Any other ways to loop without using VBA?

    Hi all, I have a set of fixed asset data where the categories the fixed asset are only showed at the end of each category (instead of the beginning). I would need to have the category to be in a new column beside every asset number. There are over 10,000 rows of asset and 20+ categories...
  16. I

    Need help to sum live data in 2 week increments.

    My goal is to understand which two week increments of a marketing campaign were most/least effective. Column A: live data for a marketing campaign (leads generated) Column B: Sum up the live data up until 2 weeks after the start date of the campaign. After the 2 weeks is complete, I would...
  17. F

    SUMPRODUCT IF value equals

    Hello, could you please kindly help me with the below, to make it so that it'd only consider values for the calculation if the corresponding value in column 'A' = "XX", and instead of specifying the range, sums up all the matched values in a given column? Please also note that values start in...
  18. M

    function too short message.

    =COUNTIFS(C:C,"Project Director/Manager-Civil",D:D,"temp",(or(E:E=may-20,E:E=mar-20,E:E=jan-20,"ans"))) i am trying to count the number of "Project Director/Manager-Civil" in column C which has text "Temp" in D and finally on E it could be any from "Jan-20 to dec-20", this would work if i...
  19. V

    Creating a ifs function - spill error

    I would like to create a formula for each customer (column A; i.e. customer 1 made 8 purchases, customer 2 made 20 purchases, etc...) to calculate a ratio in column W based on column V. For example, cell W5 should return 0.75 (3 divided by 4; 3 comes from cell V5 and 4 comes from 4 observations...
  20. E

    Any way to replace "IFS" in Excel 2016?

    I have a deadline tomorrow and I need to change the below code to something that works for Excel 2016. Right now I am using the IFS formula, but it is only working in Excel 2019. Is it any way to rewrite below formula below for the 2016 version or am I screwed...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top