if and

Hello! Trying to build a formula that counts an employee as 1 if they are active and had a start date prior to the month in cell E3, F3, G3 and so on. If they are termed, than I would like to pull a 0 in subsequent periods, but keep the 1 where there were active. For instance, on john doe, I...

Dear All, I currently have a spreadsheet which is to be used by others to track their monthly and quarterly payments. On sheet1 I have set up formulas for the user to input their monthly points data, and my formulas use this data to calculate the corresponding funding achievement in Column P...

Hello! Our firm is advertising for 2 different groups at the same time. 1 group has a potential for 3 different ads and the other has 2. Ads can overlap during the day and when they do, the expense is split evenly, but only for the 1st overlap. any remaining overlap is solely the...

Hi, I'm working on an interactive spread sheet to use with the teachers that I support and I'm trying to find a way to make the cells in one column display certain text based on the conditions in another. I've literally spent the equivalent of two work days, it feels like, playing with formulas...

ABCDEFGH1SkuCompetitor SkuCross 1Disti 1Cross 2Disti 2IF ANDExpected Results211111x11xZ11xXZ and XZ and X322222x22xZ#N/A#N/A#N/AZ433333x33xZ33xXZ and XZ and X544444x#N/A#N/A#N/A#N/A#N/ANo655555x55xZ55xXZ and XZ and X78Disti 1 SkuDisti 2...

I am stuck on this IF AND statement that adds additional conditions. I am able to find one condition but I am trying to add on to it when other conditions are met within the same cell. e.g. IF(AND(B2="TEST",C2="one"),"Good","") this above statement worked to fine this condition but how do I add...

Sub CopyToSheets() Dim ws As Worksheet Dim ws2 As Worksheet Dim lastrow As Long Dim lastrow2 As Long Dim rownum As Long Dim ws2name As String Set ws = Sheets("RAW DATA") lastrow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row For rownum = 2 To lastrow Select Case ws.Cells(rownum, 8)...

Hi all! I currently have a large worksheet that is similar to this: With the master data looking like this: I want to create a sort of IFERROR(IF(INDEX=0, ,MATCH and MATCH formula (with an iferror that returns "" (blank) if there's an error)) for every column with [] such as [DATE]. So...

Hi, Need to count 1 every time a cell in range 'CHO1'!E2:E1000 contain the letter Y And the corresponding cell in range 'CHO1'!H2:H1000 contain the letter Y. =IF(AND('CHO1'!E2:E1000="Y",'CHO1'!H2:H1000 ="Y"),1,0) does not return anything. I would expect to return 2 for below.

Hello, I am trying to highlight a collision if the date ranges in columns B and C coincide as well as the values in column F. The collision column G should then be either red, or indicate yes or an x. How would I express this? Thank you!

Hi Everyone, I'm trying to figure out a formula to show if a request was rejected or approved. We make trackers twice a day and the only way to notice if a request is rejected, is if it shows up twice but with a different date. Providing example 135969 HIERARCHY HIERARCHY ARTICLE_CHANGE...

I am attempting to create a formula in B6 that considers the following criteria: If SUM B1:B2 <100 then SUM(B1:B3)*28 If SUM B1:B2 >100 then multiply the first 99 hours by $28 and every hour worked thereafter by $32. Then add B3*28. * B1 & B2 are the only hours to be considered regarding <...

Thanks in advance for any help! I'm trying to locate the month and year contained in a row of one worksheet that matches the month and year contained in a cell on a different worksheet. Then copy the matching column to a third worksheet. With the below, I'm receiving a "Type Mismatch" error on...

Hello all - If anyone has a moment to help with this, I would be greatly appreciative, thanks - In O365 excel tells me there is a problem with my formula. I have tried a good many versions of this trying to work it out, and (think) it is in the parentheses. If this is indeed the case, and you...

Goal: IF Column E is Blank AND Column M OR Column T are <0 True= "Review" False= Blank I can write the 'if/and' but when I include the 'or' my results are meaningless.:confused: Any help appreciated, as a solution will save me days of work.

Hello All, I am in need of some help with a hard IF/AND Statement with multiple variables My current Formula: =IF(T$4=$J9-1,$Q9*0.3,IF(T$4=$J9,$Q9*0.5,IF(T$4=$J9+1,$Q9*0.1,""))) Definitions T$4 = is my header date (Formula is dragged left to right in the same row $J9 = My Starting Date...

Hi I'd like to write a formula that i) checks that J1 is not blank i) checks if the word "for" is NOT in a cell J1 ii) checks if cell K1 is equal to 0. I've tried this formula but it's not returning the result expected. =IF(AND(J1<>"",K1=0,COUNTIF(J1,"*for*")),"Code","Blank") Please...

I need help with creating the correct formula for the Employer Matching column (Column G). The employer matching formula is the lesser of 50% up to 6% of Gross Wages OR a maximum of $8,400 (which is 3% of $280,000). Once Gross Wages reach $280,000, the employer match will no longer accrue. Also...

Hello, I am trying to do a If And with a vlookup within the same workbook. IF(AND(IF(AF3431="Red",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Blue",VLOOKUP(AG3431,'VLookup Table'!E:F,2,FALSE),IF(AF3431="Green",VLOOKUP(AG3431,'VLookup...

I need to build logic for below statement: If 2018 Favorability % is greater than 2017 Favorability %, and if 2018 Favorability % is greater than or equal to 70% and if the difference of Favorability percentages 0f 2018-2017 is greater than or equal to 10%, then “positive”, other than that...