and if

  1. L

    If-And (?) Loop construction (?) to delete multiple rows based on multiple conditions?

    Hi all, I am struggling with this for a while... I have the following excel sheet as an example data set Column A Column B Column C Column D 1 First names Last names Fruits...
  2. D

    IF formula not working

    Working on metadata for a music library. I'm having problems with an IF formula, there are five possible outcomes for the cell named "Tempo": Unspecified Very Fast (131+BPM) Fast (111-130BPM) Medium (90-110 BPM) Slow (0-89 BPM) In my formula, S2 is the BPM value of each song =IF(S2<90,"Slow...
  3. N

    Multiple IF plus an AND and OR - is it possible?

    Hi I feel like this is probably a bit must to ask but lets see - i have 3 pieces of data for Income / Debt / Credit. For the purpose of this A1 = Income, B1 = Debt and C1 = Credit i need to create a % score based on 4 variable produced by those bits of data. IF B1+C1 = A1 "0%" IF B1 + C1...
  4. A

    VBA Code - IF AND statements to identify rows, move to another tab and then hide

    Hi all I'm hoping you can offer some advice please!! I have googled as much as I can to get to this point. I have a workbook, and I would like some coding that identifies if a contract is a "Contract - Framework" AND has a status of "Contract Awarded" on the "Procurement Tracker" tab. I would...
  5. C

    Complicated IF(AND's

    Hello, So my brain doesn't have the power to figure this one out so if someone has the cerebral capacity to solve this I'd be eternally grateful. Attached is a 'calculator' that works out the volume of chefs wear needed for each of the hotels within the hotel group I work for. The plan is to...
  6. R

    Can I combine AVERAGEIFS, SUMPRODUCT, and AND?

    <tbody> Day Train Passengers Fri 702 5 Fri 752 10 Fri 757X 20 Fri 737X 2 Fri 738X 1 Fri 700 100 Sat 738X 100 </tbody> <tbody> List 702 752 757X 737X 738X </tbody> I want the sum of passengers that meet the following two criteria: 1)Rode the Train on a Friday, 2)Rode...
  7. N

    Formula with "if" "and" between two values "sumifs" data from pivot table

    Hi Mr. Excel, I am stuck on the following formula. =IF(OR(Nmd1>="200",Nmd1<"300"),SUMIFS(A1,Nmd2,'Sheet1'!$A161),"0") Nmd1 & Nmd2 are both named ranges that link to data in a pivot. Example as follows "=+'DATA PULL DOWN FROM SYSTEM'!B4995" This formula works, but I need this to return the...
  8. X

    HOUR(IF(AND( Formula Help... Please!!

    Hi, I am having problems combining the HOUR, IF and AND functions in a formula and I'm hoping someone can assist. In cell A1, I have 09:00 and in cell A2 I have 17:00. Cell A3 has the following formula: =HOUR(A2-A1-1). This gives me 7.00 hours which is what I want. The '-1' is used to...
  9. M

    Easier Way for IF statement and payroll formula

    My control value is D46. My data is A7 to A141 for the min number and B7 to B141 for max, and C7 to c141 for the amount. I used the following formula and it works perfectly. I need to include all the data down to row 141. Is there an easy way to include all the values from rows 7-141? (other...
  10. T

    Certificate tracker: help to distinguish expiry status based on dates (DATEDIF function?)

    Hi, I am using Microsoft 2010 and I am trying to create a tracker so that I can see when my training certificates are "in date", "out of date" or "due for renewal". I have a cell C1 with today's date. I have the dates of expiry of certificates in B5:B7. I would like to generate a formula...
  11. R

    Complicated Excel Formula Help Needed

    Hi - I hope that someone can help me with this spreadsheet for my scouting groups waiting list. This is what i want to do. If i have a child's DOB in a cell, another cell (say A3) then calculates their age at today's date. This is what i want to do in another cell. If cell A3 is less than 6...
  12. F

    =COUNTIFS - the name matches one of the names in a range

    I need my COUNTIFS formula to only count if one of the people who appears in column F is also one of the people in a range on another tab here: =Key!V2:V12. So in this formula below, A_List_Import!$F:$F,'All Dates'!$A$501 is just looking for one person. But I need it to count it if the person...
  13. Z

    If , and , struggling

    Hi I want a formula that does the following: If Cell C2 = Year 2 ($B$3), then Cell C3 = 60% and then D3 = 40% with E3, F3,G3 all zero If Cell C2 does not equal B3 , then Cell C3 = 0 so if Cell D2 = $B$3 , then D3 =60% and E3 = 40% and all cells afterwards F3, G3, are zero.....and so on
  14. M

    IF formula to return a value but if no value perform a calculation

    Hi! I have been racking my brain trying to find a formula that will do the following: IF I3 states "Not Recv'd" to return "Not Recv'd" but if no text exists in cell I3 then perform this formula IF(J3>=I3,"1","0"). Can anyone help me put these together? Thanks! :confused::confused:
  15. C

    Check Column A for a match to text, Check Column B for a match to a date & put X in Cell

    I am trying to work on a 5 day a week 6 - 2hr slot daily schedule for the month of October. On one sheet I have the time of slot in Column A (4 thru 33) and on that same sheet I have a popup calendar that inserts the day of the event in Column B (4 thru 33). On the second sheet I have a...
  16. F

    if and condition in excel

    Firstly I want to work with field I3. I should input there a formula that compares H3 with all the values in column B and returns the Fields that are equal to H3. Later on it should check field A* where the star is the correspondent field in A of the field that was equal with H3 in B. If one of...
  17. A

    IF FUNCTION with multiple conditions

    If number is 1 to 4 show "Scene 4" If number is 5 to 9 show "Scene 3" If number is 10 to 24 show "Scene 2" If number is 25 to 35 show "Scene 1" And if number is 0 or above 35 show "Invalid" Please help.
  18. M

    If & And Statement Help

    I need a formula in cell U3 If the value of s3>=5, then add the value of T3 with a cap of 5 if the value of G3>=25 but if the value of G3<25 add the value of t3 with a cap of 3 only if s3>=3 HELP!
  19. C

    Advanced pasting - IF condition is met

    Hi, I have a long sequence of numbers running horizontally on one tab of a worksheet. I want to paste this set of numbers into a grid on a separate tab, if the column ref = the row ref ONLY IF the value in column C > 0. It should look like the below, so as you can see if column C (to the...
  20. C

    IF OR functions, unless a certain condition is met

    Hi all, What I want to achieve is this: Display me a set value e.g. 10, every time that EITHER condition A or condition B are met, UNLESS a given cell = 0. To give some context, this is relating to deliveries of a product. I am forecasting the costs incurred over a period of years where...

Some videos you may like

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...