-
Ignore blanks while detecting matching text across multiple columns
Hello! I am trying to write a formula for detecting ANY matches between "Affiliation" and "Affiliation 1," "Affiliation 2," or "Affiliation 3". If any of them match "Affiliation", then "conflict" is true. If false, it should say "none". However, if "Affiliation" is blank and matches another blank, it also outputs…
-
Summing values for a specific date from a date range
Hi all, I have a project sheet with Start and End dates for tasks, as well as a Percent of Team's Time column that tells me how much of my team's capacity will be used on that task. I want to sum up how much of my team's capacity will be used on any given day, since there are multiple tasks with overlapping date ranges…
-
Need Formula help
Hello, I need a formula in order to calculate the sum of specific hours for a resource on a particular week with a certain criteria. For example, I need the formula that calculates the hours for Jane Doe for week ending 4/2/21 charged only to Department A. I tried something with SUMIF or IF(SUMIFS but couldn't get it to…
-
Guidance requested- Count if two drop downs options are both selected in one cell
Hi I'm trying to get a count of the number of times 2 specific dropdowns are selected in a given cell in a specific column, where the project line item does not have a specific dropdown selected in separate timing column. e.g. count the number of instances where the [Sequencing Dependencies] column has both "Dropdown…
-
Sum Row with Current Month for Metric Widget
I've search and tried this so many different ways but cannot figure this out. Basically, I want to create a Sheet Summary Field that totals the row for the current month. I thought creating a helper field and index formula would do it - but can't get it to work for some reason. I think I've over complicated it, honestly.…
-
Validating data input in a Form
I have set up smartsheets to collect data with forms for the users at our facilities. The operators check equipment and enter the data in the form. Sometimes, they fat finger it - or forget the decimal point which causes an out of spec condition and email notifications. Is there any way to validate the data and the user…
-
Column Number Function
Hi is there a function that can return the column number when given the column name? This would help when columns are added in sheets that have vlookups referencing them.
-
Issue with COUNTIFS formula with OR condition
Hi, I have an invalid operation error with the formula below. I want to count all active projects for environment = Reed Services, Reed Saas, V4. Any idea what's going on? =COUNTIFS([Project Status]:[Project Status], "Active", Environment:Environment, "Reed Services", OR(@cell = "Reed SaaS", @cell = "V4")) Thanks!…
-
2 Formulas Required
I'm trying to find a formula that will calculate the financial quarter based on the file review date I have used numerous formulas from the community but none appear to work. This is the one that's closest however, as you will see from the screen shot the quarters aren't calculating correctly =IFERROR(IF(MONTH([Date of…
-
Days Open if Task Status is Closed
I am new to Smartsheets and I am trying to show how long a task has been open since the project Start Date, but, if the task is marked as "closed", I want the "Days Open" column to record the Date Completed minus Start date, but if task is still Open or In progress, I want the "Days Open" column to record the number of…