-
Countif element of a cell is one of a few options
I am trying to find a formula that will return a count of how many elements of a single cell match my criteria. I am thinking some combination of a Countif, CountM... something? But I can't quite get it. Here's what I'm trying to do specifically: I have a contact list column that allows multiple people from my entire…
-
Auto Add a set of rows
I have a data entry sheet set up for the year. Image below. I will have forty of these. Each of the forty sheets will have 10-15 children below the ALL parent row you can see. Is there a way to have smartsheet automatically do this .... If I have a master parent/child row sheet is there a way it will adjust if I adjust the…
-
Pesky COUNTIFS with OR formula
Hi everyone, I have one of those pesky formulas that is giving me a hard time. This is for a Summary Sheet I'll use to build a dashboard so I'm referencing another sheet with the data. Here's what I am trying to pull...Count if the following is true: 1) Project name is the same as the project in the summary sheet 2) Month…
-
Formula to determine is something is due this week
Hi all - I would like to calculate if something is due this week based on a target date in another cell. I have tried this formula: =IF((TODAY() - [Week Of]1) - 7 > 0, "no", "yes") This formula pull in a 'yes' for anything that is due in the next 7 days, which is not quite what I'm looking for. Is it possible to determine…
-
Sumifs with multiple criteria options within same criteria range
I am trying to write a formula that will count the # of days that are considered "Time Off". I've put a filter on my source sheet with the needed criteria and need my formula to give the # 11. Essentially - look at Sheet (Field Movements). Give me a sum(total) of the "Da..." column (which this would equal 11) Criteria…
-
Search a data column and return the most recent past Saturday.
Have not found this in the community, videos, courses, or formula template. CONTEXT: I have a dashboard that updates weekly. It has weekly data and a rolling 13 week chart. The date column is a Saturday (weekending date) and the team completes it when they come in Monday for the week prior). I have a data collection sheet…
-
Average if date falls in a month and contains certain text
Still learning average formulas! Trying to come up with a formula to average the number of items where the despatch date is in a certain month (ie January - not of a particular year but all January's) and if another column contains certain text. So it will average the number of items in January if the country column is…
-
Cross-sheet references with Index/Match and reference sheet has multiple rows that match
Sheet A: Formula in Data: Ent. Install Time: =IF(CONTAINS("Enterprise 2022 R1", {AtRiskAnalysis-Version_Version}), INDEX({AtRiskAnalysis-Version_EntInstallTime}, MATCH(CustomerId@row, {AtRiskAnalysis-Version_CustomerID}, 0))) Formula in Data: EDIS Install Time: =IF(CONTAINS("EDIS 2022 R1",…
-
Formula to split up / break down long strings of text
Hi, Hope you're all keeping well. I was hoping someone could point me in the right direction of a formula / function that can help break down large sections of text. If someone could point me in the right direction, I should be able to work out the nuances and implementation myself. I have an API that exports data into a…
-
Activate/Deactivate a Checkbox
Is it possible to activate/deactivate a checkbox, based on the value in another cell? For instance, I have a sheet where users enter data via a form. Then there is a "DQ_Check" column with an IF statement that returns "TRUE" or "FALSE" to indicate that all required fields are valid. If that statement returns true, I want…
