Formulas and Functions

COUNTIFS within a Date RangeQuestion

Answered - Pending Review Dean Arvan 13 views 3 comments 0 points Most recent by Paul Newcome 07/10/20

First post 07/09/20,Last post 07/10/20
Hello, I have a column of dates that are formatted as dates. I am trying to use COUNTIFS to tally the number of instances in which a date from the column is less than 1 month from today, between 1 and 3 months from today, between 3 and 6 m…
Formulas and Functions133
Dean Arvan
Formulas Not PullingAccepted Answer

Accepted Answer Stephanie Bauder 12 views 3 comments 0 points Most recent by Andrée Starå 07/10/20

First post 07/08/20,Last post 07/10/20
We currently utilize a very large global onboarding smartsheet. When a new hire accepts a position, the onboarding team uses a form to manually add them to the tracker so we can assign out the onboarding tasks and track progress. As part o…
Formulas and Functions123
Stephanie Bauder
Automatic date update in a cell when another cell's value changes (as calculated by a formula)Accepted Answer

Accepted Answer Emma Smyth 91 views 14 comments 0 points Most recent by Andrée Starå 07/10/20

First post 05/19/20,Last post 07/10/20
Hello I would like to have a field set up that updates the date every time the 'status' changes in another column. Is this possible? For example, when a new line item is added and the status column changes to 'on hold' I would like the da…
Formulas and Functions9114
Emma Smyth
Copy and move row workflow disabled by smartsheetQuestion

Answered - Pending Review Katherine Britt 8 views 3 comments 0 points Most recent by Andrée Starå 07/10/20

First post 07/10/20,Last post 07/10/20
My copy/move row workflows (that previously worked) were disabled yesterday. I now have an error that says "Rows can't be added to the destination sheet because a column has the same name as a system column on the source sheet." …
Formulas and Functions83
Katherine Britt
Adding a date cell a cell is changedQuestion

Answered - Pending Review Sheryl Walder 10 views 3 comments 0 points Most recent by Andrée Starå 07/10/20

First post 07/09/20,Last post 07/10/20
Is there a way to add a date to a row, when a status field is changed? I tried the "Modified (Date)" but that will change whenever any cell in the row is updated. I only want the date to update when the one specific cell changes.…
Formulas and Functions103
Sheryl Walder
How do I get the % to stay at 100% and not 150%?Question

Answered - Pending Review Ashley Pierce 16 views 1 comment 0 points Most recent by Cierr 07/10/20

First post 07/09/20,Last post 07/10/20
(Image) How can I get the Completion Rate to only show 100% rather than 150%?
Formulas and Functions161
Ashley Pierce
RYG formula based on completion time and statusAccepted Answer

Accepted Answer Monika Tomczak 28 views 2 comments 0 points Most recent by Monika Tomczak 07/10/20

First post 06/22/20,Last post 07/10/20
Hi, I would like to have a formula that shows me RYG status based on below criteria: -if status = completed and [Actual Finish] = [Baseline Finish Date] then green -if status work in progress (wip) or not started and [Actual Finish] is le…
Formulas and Functions282
Monika Tomczak
Incorrect network daysQuestion

Answered - Pending Review UrbanNomad 38 views 6 comments 0 points Most recent by UrbanNomad 07/10/20

First post 06/26/20,Last post 07/10/20
Hi All, I'm trying to replicate the same formula in smartsheet, the issue is network days are calculated incorrectly, is my formula wrong? I have attached the image: from Jun 3 - Jun 19 there are 13 network days, while splitting for the we…
Formulas and Functions386
UrbanNomad
How to Return Many Values to Track Status Changes

jasonp 11 views 1 comment 0 points Most recent by Andrée Starå 07/09/20

First post 07/09/20,Last post 07/09/20
We are trying to track the dates that tickets change from status to status. Using workflow, I created a new line added for each status change. But now need to return the dates on each of those lines entered. Below are screen shots of the …
Formulas and Functions111
jasonp
Count Projects by Year within a rangeAccepted Answer

Accepted Answer cmondo 23 views 9 comments 0 points Most recent by Stefan 07/09/20

First post 07/09/20,Last post 07/09/20
Hi All! I've been scratching my head with a way to count all projects where the Start Sate or End Date have a year that matches a bucket. Fore example, if a project's start date is 2020 or end date is 2020, then I need to count it in the 2…
Formulas and Functions239
cmondo
Is it possible to have a column in a sheet serve as a reference to multiple other sheets?Question

Answered - Pending Review Jamy Crum 17 views 1 comment 0 points Most recent by RossL 07/09/20

First post 07/09/20,Last post 07/09/20
Here is my scenario: I am redesigning some of my template sets and dashboards but leaving the originals up and running at the same time. I created a sandbox environment (workspace) to do this in. Some of the new sheets use the same data …
Formulas and Functions171
Jamy Crum
SUMIFS and Contains conditionsAccepted Answer

Accepted Answer Amy Martin 11 views 11 comments 0 points Most recent by Paul Newcome 07/09/20

First post 07/09/20,Last post 07/09/20
Hi all- I have a formula that refers to another sheet to sum costs based on some criteria: =SUMIFS({Professional Services Project Costs Amount}, {Professional Services Project Costs Grant Code}, $[Budget Allocated]$8, {Professional Service…
Formulas and Functions1111
Amy Martin
Lookup values associated with multiple entry cells containing cost codes

Michael McKenna 11 views 0 comments 0 points Started by Michael McKenna 07/09/20

First post 07/09/20,Last post 07/09/20
I have a schedule that contains tasks, and each task has one or more cost codes associated with it. These cost codes are in Dropdown (multi select) cells. For each cost code in a cell, I would like to look up the associated value and add t…
Formulas and Functions110
Michael McKenna
IF cell matches criteria in one column, THEN return other columns from matching rowAccepted Answer

Accepted Answer Michelle Schwartz 20 views 3 comments 0 points Most recent by Genevieve P 07/09/20

First post 07/08/20,Last post 07/09/20
Help, please! I am trying to search a MASTER sheet for ALL ROWS that match two criteria (MASTER Author-Partner matches the value in [Project1], and MASTER Project category matches [email protected]), and then return the contents of certain cells…
Formulas and Functions203
Michelle Schwartz
Trying to Use Join and Collect in a function, Says Calculating, but blank resultQuestion

Answered - Pending Review Terri-Lynn Morrison 13 views 15 comments 0 points Most recent by Paul Newcome 07/09/20

First post 07/08/20,Last post 07/09/20
I am having an issue getting this formula to work for me. This is based off a previous question asked and answered but I can not get it to work for me. Sheet "1.Master" where I want the consolidated information to go contains [S…
Formulas and Functions1315
Terri-Lynn Morrison
INDEX/MATCH or VLOOKUP for Date RangeAccepted Answer

Accepted Answer sahilhq 26 views 6 comments 0 points Most recent by Susan Hildebrant 07/09/20

First post 07/07/20,Last post 07/09/20
Hi, 1) I have sheet "X" set up as shown in screenshot 1 below where I have got a start date and end date column and the week numbers associated for the date ranges. 2) I have got another sheet "Y" set up with empty wee…
Formulas and Functions266
sahilhq
Automated workflow (email alerts) stopped workingQuestion

Answered - Pending Review Dani L 72 views 18 comments 0 points Most recent by Genevieve P 07/09/20

First post 06/23/20,Last post 07/09/20
I have a number of automated workflows set up to send alert emails that are no longer working. They were previously working fine for days (and I tested them repeatedly), but they have all of a sudden stopped. I haven't changed the automati…
Formulas and Functions7218
Dani L
Contact Cells that matchAccepted Answer

Accepted Answer Simon Wilcock 11 views 2 comments 0 points Most recent by Simon Wilcock 07/09/20

First post 07/09/20,Last post 07/09/20
Morning Pulling my hair out! I have two fields, lets call them Manager 1 and Manager 2. Both are vlookups from a central approval names sheet. Works fine and no problems. I have a situation, because both fields are used in an approval flow…
Formulas and Functions112
Simon Wilcock
Conditional Logic FormsQuestion

Answered - Pending Review Lindsay1 13 views 1 comment 0 points Most recent by Andrée Starå 07/09/20

First post 07/09/20,Last post 07/09/20
We have a form that will have yes/no logic. If Yes(problem is resolved with the previous information provided in logic) is selected we want them to back out of the form or display a "thank you, please submit if you have any additional…
Formulas and Functions131
Lindsay1
Row numbersAccepted Answer

Accepted Answer Amit Wadhwani 28 views 8 comments 0 points Most recent by Mehran 07/09/20

First post 06/29/20,Last post 07/09/20
Hi, I have seen multiple threads around this but still I'm surprised there is no dynamic solution for this, I have tried auto populating ROWID by AutoNumber column and as you can see in screenshot whenever I try to add a new row in between…
Formulas and Functions288
Amit Wadhwani
Can I Cross Reference a sheet in client account to a sheet in my account?Question

Answered - Pending Review Sam Knight 8 views 1 comment 0 points Most recent by Andrée Starå 07/09/20

First post 07/09/20,Last post 07/09/20
I'm trying to enter the following formula =SUMIFS({Barcode packing lists Range 1}, {Barcode packing lists Range 2}, [PIckup Date]25, {Barcode packing lists Range 3}, [KIT# / SKU#]25, {Barcode packing lists Range 5}, "Green") wher…
Formulas and Functions81
Sam Knight
how can I input a logical date in the following format "YYYY-QQ" (ie, 2021-Q2)...Question

Answered - Pending Review Jeff Berger 16 views 1 comment 0 points Most recent by Genevieve P 07/09/20

First post 07/08/20,Last post 07/09/20
how can I input a logical date in the following format "YYYY-QQ" (ie, 2021-Q2) so that it can be sorted and also be referenced in a formula ?
Formulas and Functions161
Jeff Berger
Having two currencies in one sheet

yooneun2 67 views 4 comments 0 points Most recent by Paul Newcome 07/09/20

First post 02/07/19,Last post 07/09/20
I currently manage vendors in Japan but need to also have currency in dollar for our finance/accounting in the US. I think the only current option is to change the whole account setting to change the currency, but that will prevent me fr…
Formulas and Functions674
yooneun2
Formula on Smartsheets not autopopulatingQuestion

Answered - Pending Review S.GC.Bot4.d.whq User 22 views 1 comment 0 points Most recent by Genevieve P 07/09/20

First post 07/08/20,Last post 07/09/20
I have a Smartsheet that has formulas setup in the first two rows of the sheet. I have data on an Excel that needs to be copied and pasted onto this sheet. On copying and pasting upto 20 rows, Smartsheet has continued to maintain the formu…
Formulas and Functions221
S.GC.Bot4.d.whq User
Is it possible to combine vlookup, join and collect AND reference an external sheet?

Brian Barscht 111 views 4 comments 0 points Most recent by Genevieve P 07/09/20

First post 03/05/19,Last post 07/09/20
I'm trying to group information together from a second smartsheet, but the information requires a vlookup so a typical join/collect won't work. In the first smartsheet I have a company ID in each row that I want to look up, and in the sec…
Formulas and Functions1114
Brian Barscht
How to calculate the number of days past dueAccepted Answer

Accepted Answer Christine Lazzaro 29 views 12 comments 0 points Most recent by Stefan 07/09/20

First post 07/06/20,Last post 07/09/20
Hi, I'm new to smartsheets! I'm trying to determine the formula for the following: The project plan has a due date and and actual date completed, if the due date has past I need to determine the number of days late based on today's date…
Formulas and Functions2912
Christine Lazzaro
How To Extract Month And Year Only From A Date and if the date column is blank Return Blank?Accepted Answer

Accepted Answer Prajna Jain 11 views 2 comments 0 points Most recent by Prajna Jain 07/09/20

First post 07/08/20,Last post 07/09/20
Hi All, I have to generate a monthwise report to show how many Requests are in a particular month I tried the following formula. I have also added IF(ISBLANK()""), so that it leaves the cell blank if the date is not there in the…
Formulas and Functions112
Prajna Jain
Summary Fields and Subtraction in Sheet SummaryQuestion

Answered - Pending Review gmb-nps-xofigo88916 13 views 6 comments 0 points Most recent by Andrée Starå 07/08/20

First post 07/08/20,Last post 07/08/20
Hi I am trying to subtract a number from one summary field (All Credits Paid) within another summary field (Pending Credits). Not having much luck. The pending credits summary field did have a number in it prior to me messing with formulas…
Formulas and Functions136
gmb-nps-xofigo88916
Fields are Missing in Form Logic ConstructionQuestion

Answered - Pending Review Doyle54 12 views 3 comments 0 points Most recent by Doyle54 07/08/20

First post 07/08/20,Last post 07/08/20
What I am trying to do is use Logic to select a Company and use that Selection go to a specific Company Field. There is a Dropdown for the Company Selection - (like BR on left) and another for the Company (like BR Field) that allows t…
Formulas and Functions123
Doyle54
country reference sheetQuestion

Answered - Pending Review aasim_raza73731 8 views 2 comments 0 points Most recent by Andrée Starå 07/08/20

First post 07/08/20,Last post 07/08/20
HI i am using country reference sheet to map a country with a cluster. when i add a country using a form a cluster will map accordingly. some how this is broken can any one help me please
Formulas and Functions82
aasim_raza73731

Formulas and Functions

Formulas and Functions

Discussion List