-
Trying to combine IF, OR, & AND
Hi all, As the title states, I am trying to create a helper column that is checked or unchecked based on a few conditions. I want to check the helper column box if statements 1 and 2 are both true or if statements 1 and 2 are both false. I had written: IF(OR(AND(statement 1@row = 1, statement 2@row = 1), AND(statement…
-
Shortcut for nested IF function
I am tying columns together in a sheet so that when the user selects a product from a dropdown from a cell, it will automatically populate the product's manufacturer. I can do this using the following: =IF([Product Description]@row = "Product 1", "Company 1", IF([Product Description]@row = "Product 2", "Company 2",…
-
Countifs when there are different answer options
Hello, I want to collect data on the number of times certain machines are reported between certain dates Here's is the basic formula that works. =COUNTIFS({NS Repair Detail Report Type}, "OOB", {NS Repair Detail Machine Model and Description}, "Machine Name 1", {NS Repair Detail Activity Date}, >={Sept 1 2022}, {NS Repair…
-
My COUNTM formula will not stay
Not sure what to do here. I inherited this sheet and the countm formula will not stay. If you remove the apostrophe it reverts back within a minute. How can I keep the formula?
-
Average for both columns
I have a dropdown columns with several choices and each choice represents % complete. The idea is to get the Circuit % complete for 1st and 2nd circuits columns but also It has ‘N/A” that will not count. It only count the column that doesn’t have it. This is what I tried: =AVG(IF([1st Circuit Status]@row = "Assigned to…
-
join collect
I have a formula that returns as expected =IF(ISBLANK([unique.invoice]@row), "", JOIN(COLLECT(collect:collect, [Invoice No.]:[Invoice No.], [unique.invoice]@row), CHAR(10))) but I tried to replicate the formula to join collect another column and its not working =IF(ISBLANK([unique.invoice]@row), "",…
-
Lookup multiple columns for text value and return cell value
I'm trying to figure out a formula to return the value in a cell over multiple columns. I have 10 locations consisting of various buildings for each location. Only one column will have text in it. The formula I have will obviously only return the text for that column and I cannot figure out how to repeat the formula to…
-
Combining IF Statements is not working with comma
Hello, My formula is as follows: =IF(AND([日付 Date]@row < TODAY(), CONTAINS("1号室 Room 1", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Red"), (IF(AND([日付 Date]@row <> TODAY(), CONTAINS("2号室 Room 2", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])),…
-
Trying to use COUNTIFS and keep getting UNPARSEABLE error
Hi, I've entered this statement: =COUNTIFS(PMO Project RAG Status Range 5, <DATE(2019, 12, 31), (PMO Project RAG Status Range, "Corporate Support - Corporate Finance") What I'm trying to do is count the number of projects with the designation of Corporate Support - Corporate Finance that will be delivered in 2019. The…
-
Progress Bar Date Automation
Hi All, Could someone help me figure out a formula for the progress bar column style. My goal is to determine how close (by progress bar visual) are we to the "target end date" of a task. For example, my start date is 01/09/2022 and my target end date is 30/09/2022 and the actual date of today is the 29/09/2022 so we would…