-
Commas populating from an unknown place
I wanted to make metrics that tell me peak hours my team is receiving requests to staff accordingly. Since Smartsheet doesn't seem to be able to calculate time, I used the Right formula to grab the time and mixed in a find formula to to account for the change in character length. I currently have 2 sheets that calculate…
-
Syntax help for COUNTIFS with DESCENDANTS
Hi there! I have two separate COUNTIFS formulas that work individually on their own. However when I try to combine them into a single COUNTIFS formula I get an Incorrect Argument Set error. Here's what I've got: Successful Formula #1 - counting the descendants of Row 1 where the Simplified Status column is "Not Started" to…
-
Quick poll: Case sensitivity of LOOKUP and MATCH
Hi all, Our current LOOKUP and MATCH functions are case sensitive, e.g., "my task" will not match against "My task". Would folks expect these functions to be case sensitive, case insensitive, or do you have no preference? Thanks, Daniel
-
Get Average of "checkboxes checked" for an entire column reference from another sheet
Hi Everyone - I'm trying to get an average of "checkboxes checked" for an entire column range. I came up below but it's not working. =COUNTIF({1 Main Speaker System Range 2}, 1 / COUNT({1 Main Speaker System Range 2})) I was successful to get an average of a row range because I divided by a fixed number. Here is the…
-
COUNTIFS with month and year
The data that I have is on the Date:Date column. I want the formula to return the amount of cells that meet the month and year criteria. I have the following formula and I'm not sure what's wrong but it gives the #unparseable error. I have tried many different formulas from other posts but can't get it right.…
-
Calculate with dates
Hey smartsheet-community, I am trying to create a sheet where the end date is fixed. From there, I want to subtract different number of days (at best only working days) based on the complexity of individual steps and thus get the date at the end on which the project should start. Here an example that hopefully helps to…
-
Add days to a date
Hi, I am trying to create a column that adds a certain number of days to a date. When I use =[Task start date]@row +7 . It doesn't add 7 days to the date, instead just adds the number 7 to the end of the date. I have also tried to use the WORKDAY formula as an alternative, =WORKDAY(Task start date]10, 5) . This results in…
-
Formula to populate a "Year" if Create date falls between 2 column dates
I created a form that request the Calendar year for funding. They range from 2022 to 2028 and each year has Quarters. Sample quarter is between April 15 - July 15 so if I want the formula to populate me a Calendar year 2022 if the create date falls between the April - July dates. Here is the formula that works but is…
-
Count Days Since Ticket Opened
Hello, Needing to calculate number of days since line item appeared when Date Submitted column is "Auto-Number/System Generated Column Created (Date)" -- and then display "Closed" when Checkbox is checked in Complete column. Can't get the "Complete" to appear -- just goes blank. =IFERROR(IF(Complete@row = 0, TODAY() -…
-
Using Index Collect formula when there are multiple matches in the source sheet. (#Invalid Value)
Hello, My goal with using INDEX(COLLECT)) was to obtain information based on 3 different criteria, but in my source sheet there are multiple matches for the three criteria specified. What do I need to add to the Index, Collect formula to overcome this or do I need to use a different formula combination? Below is the…