-
IF Formula between two dates
Hi there, I'm trying to write an IF(AND formula that will calculate the correct number of work hours based on start and end dates and % of commitment. Row 1 = Column month being calculated (07/01/23 = July 23) Row 2 = Number of workable hours in the month Currently using the formula below -- it wasn't coming up with any…
-
Count the number of rows where a column (multi select dropdown) has more than one value
Hello I would like to count the number of rows where Attendee Role which is a multi select dropdown has more than one item selected regardless of the item. Countif({Attendee Role},"*,*") Returns 0 CountM({Attendee Role},"*,*") returns 187 which is the number of rows with 2 selections x 2 counting each selection once (I…
-
Conditional Formatting - Single Cells
Hi, I have a training spreadsheet for my staff, that has two columns, one for a required level and one for the completed level. I want it so that if the completed and required columns don't match in a particular cell, the either of the completed or required cell highlight to another colour. This will make is easier to spot…
-
Count empty rows adjacent to a column that includes a specific selection.
I am trying to count the number of empty rows in the PM Action Description column adjacent to the ImprovementComments column where only the Actionable Comments is selected. Can someone help with the formula to do this. I don't know where to start.
-
Displaying a symbol in a column based on the combination of values in two other columns
Hi, I am trying to figure out how to display a symbol based on the values of the Result column and the Status column as follows: Is there a way to accomplish this with a formula or do I need to use reference sheets? Thanks!
-
Are grouping on reports not possible with columns that use an index formula/have cell-linking?
I wanted to create a grouped report by "Assigned To", but we use an external team roster sheet and an index formula to index the role and fill in the Assigned To column. My report is saying that the "Assigned To" column is blank, even though it is returning values from the Team Roster Sheet.
-
Count cells that do not contain specific text and are not blank
Hello, I am trying to count all cells that do not contain the text "gap" and cells that are not blank but I'm having trouble. Below are the two formulas I've tried. =COUNTIF({Range}, NOT(HAS(@cell, "Gap")AND(@cell, <>""))) -> Error: #UNPARSEABLE =COUNTIF({Range}, NOT(HAS(@cell, "Gap"), NOT(HAS(@cell, <>"")))) -> Error:…
-
Formula for Average If
I read you have to use COLLECT if you want to average using multiple conditions. I want to average the difficulty rankings of all projects with the same project lead and project status. The difficulty rank is Difficulty. The project status is the Metric@Row. All I get is #INCORRECTARGUMENT. Help? Example formula…
-
Index Match not returning desired value
Hello, I am trying to pull information from one sheet into another using a "Dept ID" as the key. I've done this several times but for some reason, the formula is returning data from the wrong columns, and I can't figure out what is wrong. Current Formula: =INDEX({Department Operations Portfolio}, MATCH($[Dept ID]@row,…
-
Column Formula + Reports
Curious why a column that has a formula applied cant be changed when it's pulled into a report - from the report? Also wondering if there is a way that is quick to remove the formula from an entire column? I've switched one back to 'cell formula' to allow for edit from the report but, would like to avoid running down 600…
