-
COUNTIFS and SUMIFS with a Date Range
Hello, I'm trying to count the number of cells that have a date in the year 2020. This is the formula that is returning "0" which is incorrect: =COUNTIFS([Column 1]:[Column 1], <=DATE(2020, 12, 31), [Column 1]:[Column 1], >=DATE(2020, 1, 1)) Additionally, I want to sum the values that correspond to this date range, and…
-
Sender of Workflow emails
Hello, we are currently working on an solution where we need to collect input from users outside our organization. While the workflow works fine, emails appear as send from "automation@smartsheet.com". To make it more likely for users to respond to our request, we would prefer it being send from one of our company…
-
Children from a different column
I'm working on a stock inventory program for my company. The Parent row is the Running total, min/max barcode, description and so on. The Children will be the stock coming in and going out. I can on the Parent row Sum Children in IN column an then Sum Children in OUT column them have the formula in Parent Stock cell…
-
Status corresponding to % Complete
I would like to figure a way for the Status Column to auto fill depending on what the % Complete says. For instance: 0% = Not Started 1-99% In Progress 100% - Complete Conditional Formatting is not the way to go and I can't figure out the right If/Then statement to use. New to this world so don't think down on me too much.
-
Is there a way to return the row location of all instances of a duplicate value within a column?
I've created a duplicate flag column using the following formula: =COUNTIF(Email1, Email1) + " of " + COUNTIF(Email:Email, Email1). This shows the instance and the count. What I would like to be able to do is provide the row numbers of the duplicate emails. Match will only find the firs duplicate but won't return all of…
-
Consecutive Count of Children
Please help. This seems like it should be a fairly simple answer, but I’ve worked on this for hours with no success. I would like a count formula that counts the Children, and numbers them consecutively. I can then use an embedded IF statement to calculate specific hours attributed to each Child. This simple formula,…
-
Calculating Total Working Hours between Sign In & Sign Out
Hello, I'm facing difficulty of summing up Total Working Hours in the worksheet. I'm working on calculating a simple total working hours for my employee, they just fill in Smartsheet form then the worksheet will calculate it. I need help on which solution work best on this project. Below I provide the details: Employee…
-
Pie Charting a Sheet Summary Report
I have added a RAG status to all my projects in the Sheet Summary view. I can pull a report showing the projects and their RAG status fine. When I then try to pie chart the RAG status onto a dashboard this is not possible as the RAG status is not a number. So I added a number field and populated it with '1' so that I can…
-
Formula to include verbiage based upon selection
Hi all, I am working on a sheet in which I need a formula that when a status is selected in one column that additional verbiage would pre-populate another column. The picture below in the status column, when I select the word "cancelled", I would like the comments column to show the word "event is not going to happen" I'll…
-
Relative Default Date in Forms
I'm looking to do a 'Needed by' date in a form and I would like the default to be a relative Today + 2 weekdays. Anyone found a way to nicely do this? ie. Today is 4/29 , the form will default to 5/1 as the suggested Need by date that can be overridden by the user - say they give us 1week instead - 5/6 that should be good.…