Formulas and Functions

Nested IF AND ORAnswered Question

Answered Jturton 23 views 1 comment 0 points Most recent by Sean Morgan 03/14/20

First post 03/12/20,Last post 03/14/20
I am trying to get a formula for a check box that combines an if AND with an If OR. I have a column labeled Rental Billed that is a check box. There is another column that has multiple status options from a drop down. I want the formula…
Formulas and Functions231
Jturton
Calculate time by adding hours and minutesAccepted Answer

Accepted Answer Pam Grant 59 views 4 comments 0 points Most recent by Pam Grant 03/13/20

First post 01/09/20,Last post 03/13/20
I want to create an agenda with start times. I want to specify the start time for the fist item and then have Smartsheet calculate the start times of the remaining items based on how many minutes are assigned to the previous item. Any ide…
Formulas and Functions594
Pam Grant
The Average Number of Monthly ProjectsAccepted Answer

Accepted Answer Annie Lindenberg 22 views 3 comments 0 points Most recent by Paul Newcome 03/13/20

First post 03/13/20,Last post 03/13/20
I want to be able to have a formula that pulls the average number of projects we complete monthly for our sheet summary. I have a date column for "Date Completed". Can anyone help with what the formula would look like?
Formulas and Functions223
Annie Lindenberg
Index/Match/Max/Collect

Yehuda 165 views 11 comments 0 points Most recent by Paul Newcome 03/13/20

First post 07/19/19,Last post 03/13/20
Hi all, I am having trouble putting together a formula and I am hoping someone here can guide me in the right direction. We have a sheet that we enter payments we receive from our customers. That sheet has the customer name, check …
Formulas and Functions16511
Yehuda
Why is SmartSheet sometime so dumb......?

Kris Le 17 views 1 comment 0 points Most recent by Paul Newcome 03/13/20

First post 03/13/20,Last post 03/13/20
I'm trying to do something that should be so simple..... I have a several values in a column "A" in a $ format. I want the last row in the column to calculate 5% of the total of above columns. Why does the formula =SUM([Column A]…
Formulas and Functions171
Kris Le
Tracking Multiple Select ColumnAnswered Question

Answered Sarah Ko 16 views 3 comments 0 points Most recent by Paul Newcome 03/13/20

First post 03/13/20,Last post 03/13/20
Hello Community, I have a column set up in my Project Tracker for my team (of 7 functions), that allows us to select the teams we are cross collaborating with (but those teams don't own the project). As a resource, I want to depi…
Formulas and Functions163
Sarah Ko
How to make specific person from a department a contact automaticallyAccepted Answer

Accepted Answer Jonathan Forrester 14 views 2 comments 0 points Most recent by Sean Morgan 03/13/20

First post 03/12/20,Last post 03/13/20
Hello all! I have a question where I am not understanding the total scope of formulas and functions and would like help to see what could assist me. I am a Customer Success Manager and I create projects for my customers that are onboarding…
Formulas and Functions142
Jonathan Forrester
COUNTIFS results in Zero (and NOT for same reason as in rsmith's question)Accepted Answer

Accepted Answer khankoff 14 views 3 comments 0 points Most recent by Paul Newcome 03/13/20

First post 03/12/20,Last post 03/13/20
Why am I getting no errors, but a 0 (expressed as a percent as formatted) when using the following formula? Please note that I am NOT the person who named these columns or the list of values therein, but you've gotta work with what you're …
Formulas and Functions143
khankoff
Contacts, Assigned To, Resource ViewAnswered Question

Answered fred sodergren 10 views 2 comments 0 points Most recent by Paul Newcome 03/13/20

First post 03/12/20,Last post 03/13/20
I have contacts that show under 'My Smartsheet Contacts'. I've added them as resources to a sheet, with start/finish dates, and click 'Project Resource View'. Only a portion of them show as assigned resources. If they're all listed as con…
Formulas and Functions102
fred sodergren
formula helpAnswered Question

Answered Deanna Vandermeer 15 views 1 comment 0 points Most recent by Paul Newcome 03/13/20

First post 03/13/20,Last post 03/13/20
I need to write a formula to count the number of projects in a sheet but subtract all projects that are tagged (checkbox column type) if the cyclical column is checked. Does anyone have any suggestions or help they can share?
Formulas and Functions151
Deanna Vandermeer
Number of duplicates

Pltrmgrl 82 views 7 comments 0 points Most recent by Paul Newcome 03/13/20

First post 08/24/18,Last post 03/13/20
Hello, I'm brand new to Smartsheets. Is there a way to find the number of duplicates in a column for a specific value? For example, how many "John Smiths" are in a list of names? Many thanks, Pltrmgrl
Formulas and Functions827
Pltrmgrl
Problems with formulasAccepted Answer

Accepted Answer SharonR 19 views 5 comments 0 points Most recent by SharonR 03/12/20

First post 03/10/20,Last post 03/12/20
i am having difficulties with my formulas . I am trying to acheive the following: IF status row is not "complete" AND due date is Today or in the past, if true Flag, if not unflag. My formula is: =IF(AND(Status28 <> &…
Formulas and Functions195
SharonR
'Most Common' FormulaAccepted Answer

Accepted Answer Travis Duehr 22 views 3 comments 0 points Most recent by Paul Newcome 03/12/20

First post 03/03/20,Last post 03/12/20
Hello, I am trying to create metrics on tracking the versions of an applications we are developing, currently I am attempting to find which version is most successful after it has been run through a series of criteria. Example: Build 30 - …
Formulas and Functions223
Travis Duehr
Max Collect function not working with dateAnswered Question

Answered remkin 62 views 7 comments 0 points Most recent by Genevieve P 03/12/20

First post 01/26/20,Last post 03/12/20
Hey, Ive got a drivers record sheet. on the sheet the date is entered and there are columns for the driver name and the asset number. Im trying to return the date for the last time a prestart report was done on a particular asset number. =…
Formulas and Functions627
remkin
RGBY Ball and Date FormulaAnswered Question

Answered Aimee Colton 20 views 5 comments 0 points Most recent by Mike Wilday 03/12/20

First post 03/11/20,Last post 03/12/20
Hello, I am trying to write a formula that can work out the following RED - if the start date has past and the complete % is 0 BLUE - if the start date is in the future and the complete % is 0 YELLOW - if complete % is 1-99 GREEN - if comp…
Formulas and Functions205
Aimee Colton
Problem with conditional formattingAnswered Question

Answered Leonardo Bello 55 views 16 comments 0 points Most recent by Mike Wilday 03/12/20

First post 03/09/20,Last post 03/12/20
I am trying to create a sheet for a moving task that changes each week depending on week number. The idea is to highlight and send out a reminder email to the person that has the task a certain week. To achieve this I have use the formula …
Formulas and Functions5516
Leonardo Bello
IF statements and checkboxesAccepted Answer

Accepted Answer Mahshad Farmanbar 20 views 2 comments 0 points Most recent by Paul Newcome 03/12/20

First post 03/11/20,Last post 03/12/20
Hi there, Currently I'm using this formula to check boxes in a column if the date a proposal is sent is in 2019. IF(AND([Date Proposal Sent]534 > DATE(2018, 12, 31), [Date Proposal Sent]534 < DATE(2019, 12, 31)) Sometimes there is …
Formulas and Functions202
Mahshad Farmanbar
Formula returning 0% instead of correct value in the sheet...Accepted Answer

Accepted Answer Joyce McMurran 23 views 9 comments 0 points Most recent by Joyce McMurran 03/12/20

First post 03/10/20,Last post 03/12/20
Not sure why my formula (using Index and Match) is returing 0% as a result of matching to another sheet? The correct data in the cell is = 55%. All other columns with same formula working correctly but one dept is showing an error o= 0%. H…
Formulas and Functions239
Joyce McMurran
Status dropdown Vs % Complete calculationAccepted Answer

Accepted Answer LisaB:-) 72 views 19 comments 0 points Most recent by Paul Newcome 03/12/20

First post 12/18/19,Last post 03/12/20
Hi all, I'm hoping you can help me with a thorny problem. I have a 'Status' column, which is a dropdown which contains 'Not Started'/ 'In Progress'/ 'Completed'. I was also using the '% Completed' column with dependencies to rollup the ch…
Formulas and Functions7219
LisaB:-)
Getting a sheet to refresh dataAnswered Question

Answered CRL Planning 26 views 4 comments 0 points Most recent by Nicholas Aspey 03/12/20

First post 03/07/20,Last post 03/12/20
I have a number of sheets that are fed data via forms. The sheets are setup to deal with this incoming data automatically and they feed selected information to Dashboards via reports and formulas that run within the sheets. The problem I h…
Formulas and Functions264
CRL Planning
Counting completion status off of a group of sheetsAnswered Question

Answered Mike Wilday 16 views 3 comments 0 points Most recent by Genevieve P 03/12/20

First post 03/11/20,Last post 03/12/20
We started a new process for course development that has tracking development of each of our courses in separate sheets. 1 course per sheet. Its easy to pull a report of how many of those courses are now in a complete status but I am tryin…
Formulas and Functions163
Mike Wilday
Counting Completed Dates Within the Last 12 MonthsAccepted Answer

Accepted Answer Donna Claes 18 views 3 comments 0 points Most recent by Genevieve P 03/12/20

First post 03/11/20,Last post 03/12/20
Hello - I'm trying to do a formula that will count the number of projects that have been completed within the last twelve months. I have one that will count within the last 365 days, but this result doesn't match the completed per month nu…
Formulas and Functions183
Donna Claes
Formula for Counts Using Two Separate Columns on Different SheetAccepted Answer

Accepted Answer Leland Harlan 19 views 3 comments 0 points Most recent by Genevieve P 03/12/20

First post 03/11/20,Last post 03/12/20
https://us.v-cdn.net/6031209/uploads/998/Z95ARHK44QCS.jpg Hello, I am trying to create a formula to pull information from the attached page (Armorel In-Process Record). I need the formula to count the number of times each inspector makes a…
Formulas and Functions193
Leland Harlan
Contact Lookup/Reference from Another SheetAccepted Answer

Accepted Answer Joseph Noel 15 views 3 comments 0 points Most recent by Genevieve P 03/12/20

First post 03/11/20,Last post 03/12/20
Hello, I am trying to work out a formula that will raise a flag if any contacts in a column (Attendees) are listed on a separate sheet (VIP). The attendees column could have multiple names listed, the VIP column will only have one name per…
Formulas and Functions153
Joseph Noel
Attempting a formula with multiple criteriaAnswered Question

Answered Andrew Darley 28 views 9 comments 0 points Most recent by Mike Wilday 03/12/20

First post 03/08/20,Last post 03/12/20
Hi, I've 2 columns named "Audit Completed by" which contains a result from a contact list and "Audit Result" which gives dropdown result of pass or fail. Trying to give a sum of when both have a specific username and pa…
Formulas and Functions289
Andrew Darley
Datamesh or VLOOKUP using two criteria to be metAnswered Question

Answered Courtney Holdt 20 views 3 comments 0 points Most recent by Genevieve P 03/12/20

First post 03/11/20,Last post 03/12/20
Is it possible to use a vlookup formula or Datamesh to transfer data when TWO criteria are met. I have a parent row which is the name of the task and then children row below showing when each employee completed the task (so three columns …
Formulas and Functions203
Courtney Holdt
Automatic Status Update

Mike Rini 21 views 4 comments 0 points Most recent by Andrée Starå 03/12/20

First post 03/10/20,Last post 03/12/20
I've setup a formula which tells me if an entry that was submitted was correct / incorrect. I've also triggered an email based on that to send an email requesting an update to the specific field in question. What I can't figure out is if/h…
Formulas and Functions214
Mike Rini
Count the number of times multiple values appear in a columnAccepted Answer

Accepted Answer Yogin Bhatti 57 views 4 comments 0 points Most recent by Andrée Starå 03/12/20

First post 01/24/20,Last post 03/12/20
Hello SS Community, I'm trying to count the number of instances two values appear together in a cell. From the following example, I would like to count how often "Contractor" and "Vendor" appear together, and furthermo…
Formulas and Functions574
Yogin Bhatti
COUNTIFS error when finite rows not setAccepted Answer

Accepted Answer fred sodergren 24 views 6 comments 0 points Most recent by Andrée Starå 03/12/20

First post 03/03/20,Last post 03/12/20
I have a formula that calculates the week number, and counts all items with the priority of 'Standard' within that week. The issue is that my 'Creation Date' column currently has 97 rows, but this will increase. The formula below works, b…
Formulas and Functions246
fred sodergren
Automate checking "Done"Answered Question

Answered System 18 views 3 comments 0 points Most recent by Andrée Starå 03/12/20

First post 03/09/20,Last post 03/12/20
This discussion was created from comments split from: Trying to combine 2 AND functions with an OR.
Formulas and Functions183
System

Formulas and Functions

Formulas and Functions

Discussion List