-
CountIFS
I am having a problem with a formula. Does the COUNTIFS have a limit on the number of "logical expressions" used in a formula? My formula below: =COUNTIFS({Opportunity and task orders}, <>"", {2025 Opportunity Status}, OR(@cell = "8.0 - Lost", @cell = "9.0 - Client Not Selected", @cell = "5.0 Awarded Contract Vehicle",…
-
Average with exceptions
I'm need to apply a function to average a column (answer is correct (8)) if the other column (The nurse is caring for a a patient who dove into a) is NOT blank. I copied this over from an excel spreadsheet. I would like to keep the average in the top row. Right now it's saying "#unparseable" as the formula is not the same…
-
Trying to add a year to this formula
So here is my current formula… =COUNTIFS({Date Completed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Staff name}, =Staff@row ) This works. It gives me a number for all observations submitted in the month. The problem is it also counts the month for the previous year. How do i filter the count by month and year? Thank…
-
Change Value in Symbol Column Based on Value of Other Column
Hello everyone! I have a Status column (Symbol type), where users can choose manually the desired status. I also have a Completed% column (Text/Number type), where a percentage is entered manually. I would like to change the value of the Status column to "Blue" when Completed% is "100%", but keep as is in all other…
-
INDEX, MATCH formula referencing other sheet works incorrectly when matching formula column
Formula that I am using is the following: INDEX({Range 1}), MATCH({Search value}, {Range 2}, 0)). In this formula Range 1 and Range 2 referencing another sheet and Search value referencing the value in the current sheet. The value in the current sheet is also based on formula and when using INDEX, MATCH with search value…
-
Index-Match-Collect failure
Hello - I have a formula that I use to find the next upcoming task showing as "Not Started" and assigned to "Joe Smith." It has worked seamlessly for months and now all of a sudden is returning an incorrect value and I am totally stumped as to why. Here is the formula: =INDEX([Task Name]:[Task Name],…
-
Formula to calculate task completion by owner by referencing parent rows
Hello all, I am looking for some help with a formula. I'm super close to getting it but can't figure out the last piece. Goal: I have a sheet tracking task completion and want to calculate, month by month, the percentage of tasks completed for each person. Setup: Level 1 rows = person’s name (owner of tasks) Level 2 rows =…
-
I need an update request to include and item name and quantity, but only edit the qty.
I need to set up an weekly update request for supplies to update quantities. If I include both the "Item Name" and "Stock Quantity" columns in the request, both fields become editable. I need the item name to remain so the person doing the update knows which of the 98 items on the list they are updating. I tried locking…
-
Recording words from Column B to Column A
Hi, I'm writing to write a formula that will record certain words that appear in column B into column A. Column B is a dropdown menu with multiple options available. I only want to record the words RED, BLUE, PURPLE, if they appear in column B and if none are present, I want the cell to remain blank. I'm struggling to know…
-
Extract 4th email address from multi select cell with CHAR(10) as delimeter
I have a multi select cell that contains 4 email address, separated by CHAR(10). I have been able to isolate the 1st, isolate the 2nd and isolate the 3rd in columns to the right. 1st email is extracted with this formula —> =IF(FIND(CHAR(10), [Names of Approvers needed]@row ) > 0, LEFT([Names of Approvers needed]@row ,…