-
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…
-
How do I bring a cell amount considering the start and end date correspond to that particular month?
HI everyone, Could someone help me solve this formula? I need to have the "revenue per month amount" cell (in gray) added to the corresponding months, considering the project will be happening in that month by reading from the estimated start and end date columns. I am currently using the following formula which is using…
