-
Having an issue with COUNTIFS with multiple criteria and referencing another sheet.
So, I am having an issue getting COUNTIFS to properly calculate. What I am trying to do is have it count rows with a status of TARP and a co-pay of $0.00. This is the current formula. =COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell), {Parent Co Pay Amount}, CONTAINS("$0.00", {Parent Co Pay Amount})) Now…
-
Trying to get the last day of the next month based on another date column's value...
Hello, Community! Current formula (returns the first day of the next month) works: =DATE(YEAR([Earnings End Date]@row), MONTH([Earnings End Date]@row) + 1, 1) I'm trying though to have it return 7/31/23 instead of 7/1/23...what am I missing?
-
How to calculate Status Indicator using %Complete and Target Finish Date
I am trying to achieve the following: IF the %Complete equals 0%, then status indicator is Gray. IF the %Complete equals 100%, OR the %Complete is less than 100% and Today is before the Target Finish Date, then the status indicator equals Green. IF the %Complete is less than 100% and Today is within 1 week of the Target…
-
Checkbox in source sheet pulls data into destination sheet
I know reports are the more favorable way to go with this but that won't work for what I need. Use Case: Departments have a tracking sheet with high-level detail. Other departments want to track various level of detail in their own sheet. Only some of that data needs to make it back to the roll up sheet for executive…
-
default formula in hidden field
I need to reference the content from one field in another field on the same row. I want the formula as the Default Value of a hidden field on a form. The formula I'm using is =[Team Member]@row. When a new form is submitted, the field with the formula shows a single quote mark at the beginning, and therefore it's not…
-
Formula for average between 2 dates
Hi all, Probably a very stupid question, but is there a formula that presents the average date between 2 dates. For example, the average date between Monday 4th Septmeber and Wednesday 6th September should be '5th September'
-
I need help with creating a formula using Countif or Countifs.
I am trying to pull data from one range that has mulitiple values. Does anyone know how to write a formula to not pull information from more than one range? I am using the formula =countif(sheet reference data), (question ie: What do you enjoy most about your holiday party? @row. This works for when the question isn't set…
-
Using an IF formula for Parents, Children rows
Hi all, I'm currently trying to see if there's a formula that can be used to affect the parent row (row above the indented row) after a certain condition has been met within the children row (indented rows). Please see the image as a reference. In this example I've set up, I'm trying to figure out if there's a formula that…
-
Project Milestones Metric Help
Hi, I'm a newbie SmartSheet Admin for my organization and am still trying to get my head around some of the ins and outs of the tool, so apologies if this is a basic question! At my organization, one of the core metrics we're supposed to report on is the number of project milestones completed within +/- 4 weeks of the…
-
Trying to combine 2 formulas into one, each formula with multiple conditions
Hi All, I'm fairly new to formulas here on Smartsheet and have been racking my head over this one to no avail so far. I basically have two formulas that I am trying to combine into one, and each formula has multiple conditions. In the first formula, the conditions to be met are that if both columns of Date Received are not…
