# and if

1. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### =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. ### If , and , ......im 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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...