-
COUNTIFS function with two criteria
Hi all! I'm currently trying to count how many red entries (in the "Puri Safety" column) are in the month of June (using the "Created" column). I'm trying to use the following formula: =COUNTIFS(Created:Created, IFERROR(MONTH(@cell), 0) = 6, [Puri Safety]:[Puri Safety], "Red") However, it returns a value of zero... what…
-
On Sheet Summary: Count # of distinct values in a column
Hello, I have a Smartsheet where I'm trying to count distinct values in a column. Currently I've tried this but get #UNPARSEABLE. =Count(Distinct(Collect(Prj_ID:Prj_ID,Prj_ID:Prj_ID,Prj_ID@row))) Where Prj_ID is the column where I want to to count distinct Project IDs. I'm hoping to get help from someone who's done…
-
How do I calculate an expiration date from a specific date column
I want to use the date in Column A plus "X" months so that Column B populates a new future date. What's the syntax I can copy/paste into Column B? Example - Column A (Contract Effective Date) - 5/29/2020 Column B (Contract Expires on Date) in 4 month. Should = 9/29/2020 ***"X" months can change as it may not be the same…
-
Nested IF with CONTAINS
I'm trying to return a value based on different comparisons of the same string. Example: Return SIL, BRZ or GLD if one of those terms are in this string : "[blah1_SIL_L01_C99A] blahserver" What works: =IF(CONTAINS("SIL", Path4), "SIL") What doesn't work: =IF(OR(CONTAINS("BRZ", Path4), "BRZ"), IF(CONTAINS("SIL",Path4),…
-
Formula needed - adding total of one column dependent on other columns criteria
I need the sum of one column conditional to the criteria of 3 other columns; below is the formula I am attempting to use with the goal of obtaining the sum of [% KRs Contribute to Corp Objs] that have the below additional columns criteria. Can anyone review and let me know if I need a different formula or what I am…
-
Hello, I've been trying to use unique values in one column to determine what the yearly sum is?
In the above screen shot, I want to use the Job Family value to add up any amounts that match for the year. I have tried SUMIF, SUMIFS, COUNTIF,.... I keep getting #unparseable error. Here is my formula: =SUMIFS(Program1:Program5, ="PAR", JAN1:JAN5, >0, FEB1:FEB5,>0, MAR1:MAR5, >0, APR1:APR5, >0 MAY1:MAY5, >0, JUN1:JUN5,…
-
How to include Paranthesis in formula.
I am using an IF formula in a Total column Column A A) Option 1 B) Option 2 Total Column = IF ([Column A]@row = "A) Option 1" , "3", IF([Column A]@row = "B) Option 2" , "5")) I am getting error, as ) is prematurely closing out the computation. Any help is much appreciated. Thank you, Deepthi
-
RAG Calculation to highlight Red, Green and Blue balls
I cannot work out how to combine the following arguments into one cell and calculate, i have spend hours trying and getting no where, would appreciate any help. The following will return a red or green to the RAG column and works perfectly : =IF([Baseline Variance]@row <= 0, "Green", IF([Baseline Variance]@row >= 1,…
-
Complex COUNTIFS and IF Function
Hello, I need help putting together a function that can count certain cells in my grid. The inputs of the function are the following: If Beta is "Yes", it should be counted twice If Beta is "No", it should be counted once But if the Primary Region Column is not checked, the row should not be counted at all. Beta is a…
-
Checking box if form submitter has selected certain options from a multi-select drop down
Hi, I am new to Smartsheet and have a form where there are about one hundred options in a multi-select drop down, they are subjects of expertise the person could speak on. 15 of the options are in the technology category of expertise. I want a checkbox to be automatically checked if the person selects one or more of the 15…