-
Using Index Match for Multiple Value Dropdowns
Hello, I'm trying to utilize index matching for some metadata sheets and using the formula I am currently utilizing, the data appears blank. The formula currently being used is: =IFERROR(INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Impact/Benefits]$1, {Portfolio Summary Header Row},…
-
Formula to check box if Date is 7 days ago
Hello all, I have an idea to help me initiate an update request to add a new row. I'm thinking about adding a helper column with a checkbox and a column formula to check the box if the Date in the previous row is 7 days earlier than today. Do you think that will work and if so, can you help me with the formula? Mine is not…
-
Formula to Sum all Selections with a Number
Hello, I'm looking for a formula that can SUM all dropdown selections that contain a number. For example, One Cell Contains: Advisor_1 Instructor_2 TA_1 Is there a formula that could sum all of these selections so that the returned value would be 4? The column is a Multi-Select Dropdown
-
calculate quarter end for corporate fiscal year
Hello! I would like to calculate quarters based on our fiscal calendar, which starts in November. I wrote the following formula to determine quarter and year. It works for all quarters except for Q1. For dates in Q1, the formula is just returning "Q1" and not the year. Any assistance would be greatly appreciated. ="Q" +…
-
Formula for counting cells with a specific date
The sheet is set up to fill in the create date for a ticket logged from a form. I need to count how many were opened each day and used the below formula for each date but the count says zero when there are line items for that date. =COUNTIF({TJ Regional Health eService Range 6}, "08/01/22") When I hover over the date in…
-
IF Statement between Ranges
Hello, I am not sure what i am doing wrong in the formula =IF([Current Risk Score]@row < 15, "36 Months", IF([Current Risk Score]@row > 70, "3 Months", IF(AND([Current Risk Score]@row > 16, [Current Risk Score]@row < 29, "12 Months", IF(AND([Current Risk Score]@row > 30, [Current Risk Score]@row < 50, "6 Months"))))))…
-
Countif fo rmultiple Criteria
I keep getting an unparseable error. I have tried the below four formulas which include referencing another page. =COUNTIFS({TJ Regional Health eService Range 3}, "Open") + COUNTIF({TJ Regional Health eService Range 3},"Client Assigned") + COUNTIF({TJ Regional Health eService Range 3},"Cerner Assigned"), +COUNTIF({TJ…
-
converting to a column formula
I am attempting to =IFERROR(SMALL([Lowest Quote]6:[Lowest Quote]9, 1), "") into a column formula I am unsure of the changes needed to make it work. I want to have the lowest quote in the children tasks to populate on the parent task bar.
-
Can I use an IF formula to return a value where the reference column is a multi option drop down??
I am trying to create an IF or CONTAINS formula that references a multi option dropdown cell to returns an email address for every department selected in the cell. i.e. 'Other Departments Impacted' CONTAINS "Sales" "Accounts" "Delivery" so the 'Associated Contacts' cell returns "John Citizen", "Fred Smith", "Tony Dory". Is…
-
Nesting IF Formula
I am attempting to create a tracker and the current formula is creating the dreaded "#UNPARSEABLE". I essentially am trying to autopopulate a number into column two, based on the name entered into column one. When I utilize the formula =IF([Employee Name]1 = "John Doe", {Employee ID Numbers Range 1}) it works fine. But…