Formulas

 

Discussion List

  • Check a box if 3 criteria (checkbox columns) are met

    I'm trying to identify registrants for a training who no showed using a checkbox column. I have checkbox columns for the following critieria: Invited Attended Declined I'd like a formula for my No Show column to return a checked box for anyone who: Was invited (box checked) Did not attend (unchecked box) Did not decline…

  • Sum of children while keeping column formula

    In my sheet I have columns with formulas that I would like to keep as applied column formulas. I also categorize my row's by creating children under a labeled row. Within the labeled row I would like to Sum up the figures contained within the columns that have column formulas however when I try to do that, the system wants…

  • Function like UNIQUE in Excel?

    Does Smartsheet have a function similar to the UNIQUE function available in Excel? The function allows you to select a range, and then provides a unique list from that list. In Excel Combining: SORT, UNIQUE & FILTER will provide a unique list excluding blanks that is alphabetized. I am constantly using that function within…

  • I would like to count the total values from a multi select column, based on another columns criteria

    I apologize but I cannot seem to wrap my head around this. My goal is to count the total values in a multi select column based on another columns criteria. I figured I could combine the use of CountM and CountIFS but I have not been successful. Here is an example. The goal is for me to determine the total # of Reps Per…

  • "Review status" based on multiple checkboxes

    Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…

  • Parent Status based on Children Statuses - Need help refining this formula

    Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…

  • Formulas not properly updating when referencing from another sheet

    Hello! I have been working with a set of formulas that do not appear to be calculating correctly when I am referencing that sheet from an external sheet. Here's my problem: In my sheet, I have recorded a "created" column that uses smartsheet's auto generate date function to record the date that the line item was made. In…

  • Help with "AND" formula

    I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an "Invalid Data type". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, "2019", {Product},…

  • Simple Children / Parent question

    Hi all, Apologies I took a break from working with SmartSheet for over a year and am slowly getting my memory back on how the formulas work. I have column called Helper, and in the CHILDREN cells it may say "Yes" if certain criteria are met. I want to have at the PARENT level, either in that column or a new Helper2 column,…

  • COUNTIF and OR or IF Formula

    I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, "Q", {Despatch Report WO Status}, <99 I basically want to count if its…