-
Combining IF and ISBLANK
Hello, I have these two formulas working separately on a four-color symbols column, but I cannot combine the two of them without getting error messages. =IF(ISBLANK([M1 Completed Date]@row), "Gray") =IF([Milestone Due Date 1]@row >= [M1 Completed Date]@row + 9, "Green", IF([Milestone Due Date 1]@row <= [M1 Completed…
-
Convert a text to value number
Hi, I'm trying to convert the answers that I got from a survey to a value that I can calcul after. Example : 13 people answer : 4 (agree) 5 (disagree) 2 (no aswer I would like to convert "agree" to value of 5 points, "disagree" for 1 points, "no answer" for 0 points. Please what should I do... Thank you :)
-
index/match based upon partial description
I have scoured the community and unable to find something that works. It comes close. but not quite. The closest I have gotten is using this formula =IFERROR(IF(CONTAINS("bricks", {Training}), INDEX({Completion Date}, MATCH([Email]@row, {email}, 0))), "") However it returns the date for the first item for pig1 (1/23/24)…
-
Metric Sheet Formula
Hi All, I'm trying to write a formula to achieve the following: In the source sheet: Check if the "Resource Type" column contains "Analytic/Data". If it does, it counts the occurrences where the "Deadline Health" column contains the value of the current cell in the "Description" column, otherwise, it returns an empty…
-
Sumif Match within a Date Range Help
Hi There, I'm working with this formula: =SUMIFS({Weekly Sheet - Company Name Range 1}, {Weekly Sheet - Company Name Range 2}, ="Initiative Name", {Weekly Sheet - Company Name Range 3}, >=DATE(2023, 10, 1), {Weekly Sheet - Company Name Range 3}, <=DATE(2023, 10, 31)) This formula pulls from a another sheet and says if it's…
-
Duplicate (countif) keeps showing false value
I am using the countif function to show if a duplicate exists, but it keeps counting when there is no duplicate. =COUNTIF(EID:EID, EID@row) It works for the previous data I created, but when I add more using the forms, it always shows 1 as a duplicate count for the column. Even when I type in random letters and numbers, it…
-
Multiple COUNTIF statements to return a value
Phoning a friend.. I am repurposing a sheet to be used for tracking recurring tasks. There may be activities either weekly or month is not required, so I want to mark the activity (parent) as “N/A” and not “Complete”. I have been able to adjust formula for everything except when all child status is are “N/A”. I believe I…
-
Formula Error
Hi, I can't seem to know where the error on the formula is. Or am I just using the wrong one. What I would like is that if there is not date on the Actual Completion Date, for it to be 0. If there is a Date, to enter the month number. =IF(NOT(ISBLANK([Actual Completion Date]@row), "", IF(ISBLANK([Actual Completion Date],…
-
INDEX/MATCH Function Trouble
Hi Community, I am not the best with formulas and having trouble. I have a Master Data sheet that has all my information about what each chemistry needs to be tested. I have my new sheet, where I want to be able to enter in my chemistry, then the sheet will pull information from the Master Sheet and fill it in, according…
-
Due date based on weekdays
I can't figure out how to create a formula that shows a due date based on 7 workdays vs. 7 days. I've tried everything I can find online, but it always shows up as invalid.