-
How to Sum a range if 2 criteria are met? (different range sizes)
Hi, I am trying to sum the total Inv. value in the below table but only for rows where the WBS target is contained in the WBS element AND where the Inv. Target Year appears in the range of Inv. years. In the below example, the the total would equal $500 because rows 1 and 2 contain 123 in the WBS Element column and the…
-
Formula Help: Find Value in 1st Row in Same Column, without using Absolute References
I'm trying to create a formula that gives me the value of the first row in the same column. [Find This Value]. There are a few obstacles: The formula is placed in the same column, BELOW value we're trying to find, in [Find This Value]13:[Find This Value]21. So it needs to be a cell formula and not a column formula And my…
-
Formula Help
Hello I am looking for assistance with a formula. What I need is '# of days for COC Completion' to equal 'Date COC was Finalized' - 'Date COC Determined' with a cavoite that if 'Was PCT hospitalized within 14 days of COC' = Yes, than instead of 'Date COC Determine' it needs to go off 'Date D/C from Hospital' Is that…
-
INDEX/MATCH showing #NOMATCH
I have 2 sheets: Source Sheet: KI Tasks Report Target Sheet: MLE Access Management All three columns in each sheet are text Formula in MLE Access ProjCode column: =INDEX({CE+RxE From KI_ProjectCode}, MATCH(ClientID@row, {CE+RxE From KI_ClientID}, 0)) Formula in Project Manager String column: =INDEX({CE+RxE From KI_PMName},…
-
If cell contains any of "apples", "oranges", "pears" in text string then collect
Hi, I have a column where I want to basically extract certain words from it without having to create multi helper columns. If I do a nested IF(OR(contains) statement or an IF(and nested statements it will look for conditions in order but I want to evaluate and return any of the certain words. I can do this with helper…
-
Setting values from form to zero
I have a form for customer feedback with ratings 1-5 and an N/A option. I'd like to apply logic such that the N/A value is set to 0. So I can work with it in other columns with formulae, avg rating, etc.
-
IF AND OR Statements
Hello! The sheet on the back end has multiple checkmarks to select what role the event is for. I am needing to create a formula that will give a Academy name based off of which checkmarks are checked. The column for academy name is a dropdown column. i.e. If support office is checked support office academy is put in the…
-
Getting sheets to talk to one another
I have two different sheets and I need them to automatically populate cell data when data is entered. I have sheet A (my department sheet) that has many columns and rows (many of which won't apply to B) and I have sheet B (another department sheet) that has a lot less columns and rows. I have set-up a column in each sheet…
-
Return latest Update
Hi Guys I have sheet - Incident Recoded, see pic below…. I have formula - =IFERROR(INDEX(COLLECT(CHILDREN([Incident Description]@row); CHILDREN(Modified@row); MAX(CHILDREN(Modified@row))); MATCH("MTC"; CHILDREN([Primary Column]@row); 0)); "") This formula works if on row 2 in the pic the Incidents is "MTC" but if the 2nd…
-
My risk formula for program row with child Projects progression is not working.
I am tryings to create a risk formula for Program that has list of projects as a child. Following are conditions to mark the project as either Green, Yellow, Red or Blue: Blue: - Project has progress (% Complete > 0) but hasn't officially started yet (Start Date is in the future) - Indicates early progress or a potential…