Formulas and Functions

Need help with using a COUNTIFS with ORAccepted Answer

Accepted Answer Jamy Crum 13 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 07/08/20,Last post 07/08/20
Hi, I'm trying to create a nested formula used in a ticketing system that will total up active status (Open or In Progress) compared to location. Ticket Active Status for Location = Location <and> Status [Open <or> In Progress…
Formulas and Functions133
Jamy Crum
Ref SUMIF example in HelpAccepted Answer

Accepted Answer Elena Codrea 17 views 4 comments 0 points Most recent by Elena Codrea 07/08/20

First post 07/05/20,Last post 07/08/20
Hi, Referring the SUMIF example in the Help article https://help.smartsheet.com/function/sumif, it is not clear to me why =SUMIF([Price Per Unit]1:[Price Per Unit]3, >20, [Transaction Total]1:[Transaction Total]3) has the result 3,115.0…
Formulas and Functions174
Elena Codrea
Creating dates to increment by month, how do I move to a new year when month equals 12?Accepted Answer

Accepted Answer Susan Hildebrant 10 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 07/08/20,Last post 07/08/20
Here is the formula I used: =DATE(YEAR(Date3), MONTH(Date3) + 1). It works great for dates up to the 12th month (December). How do I modify the formula to go to January of the next year, e.g. 12/01/20 to 01/01/21? Thanks, Susan (Image)
Formulas and Functions103
Susan Hildebrant
SUMIF for Contact Column PropertiesQuestion

Answered - Pending Review Stephanie Bauder 9 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 07/08/20,Last post 07/08/20
My formula comes back as #No Match but when I filter, I see the results I need. I am hoping you can assist! I am trying to calculate the amount of time spent for a contact person. Here is the formula I have so far: =SUMIF([Contract Phase…
Formulas and Functions93
Stephanie Bauder
RYG Ball FormulaQuestion

Answered - Pending Review Josh Canady 12 views 6 comments 0 points Most recent by Josh Canady 07/08/20

First post 07/08/20,Last post 07/08/20
Can you change formulas to make "Not Started" be the same as if it was blank? If I deleted the Not Started lines then my parent row would match my child/ children rows. (See snip # 2). I'm not sure if it's possible since you can'…
Formulas and Functions126
Josh Canady
Using CHILDREN for ranges in VLOOKUP or INDEX MATCHAccepted Answer

Accepted Answer weswillis 36 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 07/08/20,Last post 07/08/20
Hoping someone can help with this as it seems possible... I have a sheet for contacts at individual centers across a state. There are roles assigned to the contacts, so for example there may be a director, a supervisor and a coordinator. …
Formulas and Functions363
weswillis
Counting # of Employees working this weekAccepted Answer

Accepted Answer Mallory McFall 11 views 5 comments 0 points Most recent by Andrée Starå 07/08/20

First post 07/07/20,Last post 07/08/20
Hello, I am wanting to count the number of employees that are working this week. However my current formula does not take into account the number of unique names within a weeknumber. Any help would be apreciated.
Formulas and Functions115
Mallory McFall
SUMIFS function with Auto/Number system columnAccepted Answer

Accepted Answer Steph Harper 11 views 4 comments 0 points Most recent by Steph Harper 07/08/20

First post 07/07/20,Last post 07/08/20
Hi all! I'm trying to summarize deliverables using the date stamp of when a form was submitted. Unfortunately the way our form is set up, we have employees fill out a form and enter the month and the year separately so our sheet does not h…
Formulas and Functions114
Steph Harper
Using COUNTIFS to count a single name in a multi select box in a date rangeAccepted Answer

Accepted Answer Brett Kennedy 13 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 07/08/20,Last post 07/08/20
Hi all, This is my current formula, and it works except that if I have more than the name in the formula also checked in the multi select box it does not count it. How do I change this formula so it counts the name in the multi select box …
Formulas and Functions133
Brett Kennedy
How to freeze cell reference while copying a formula to the other cell.Question

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

First post 07/08/20,Last post 07/08/20
Is there any way that I can ensure that at the time of copying the formula the cell reference doesn't change? Thanks Prajna
Formulas and Functions131
Prajna Jain
List of Unique Values for Dashboard

SYSPK 894 views 25 comments 0 points Most recent by Paul Newcome 07/08/20

First post 11/19/18,Last post 07/08/20
I'd like to get a list of unique values in a range. I'm finding a lot on the community about how to flag duplicates, but not sure how to get a list where each unique value appears in a cell. Screenshot below is what I'm looking for. My goa…
Formulas and Functions89425
SYSPK
What am I doing wrong with this date function?Accepted Answer

Accepted Answer KLM 9 views 2 comments 0 points Most recent by Genevieve P 07/08/20

First post 07/08/20,Last post 07/08/20
Hi all - I'm looking to build out a repeatable process calendar. We have something starting (Intake) on X day, and then a series of activities that take a certain amount of time. I'm looking to replicate the Excel function of (Date + X da…
Formulas and Functions92
KLM
Actual Date IF Formula for RYG BallsAccepted Answer

Accepted Answer LisaB:-) 10 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 07/08/20,Last post 07/08/20
Hi all! I would like my RYG balls to express the following (actual dates, not cell reference): Green = before 21 August 2020 Yellow = after 21 August 2020 Red = after 28 August 2020 This is my current formula, and although individual state…
Formulas and Functions103
LisaB:-)
Clarification Using AVG() with COLLECT()Accepted Answer

Accepted Answer Michael Murphy 17 views 3 comments 0 points Most recent by Genevieve P 07/08/20

First post 07/04/20,Last post 07/08/20
I am looking to understand how the COLLECT() function nested within the AVG() function works and is evaluated. Using the help files, I found the following example. I am specifically looking at the very last formula where the result is &qu…
Formulas and Functions173
Michael Murphy
COUNTIF to determine the occurrence of "specific text" from another sheet.Accepted Answer

Accepted Answer Matt Foss 16 views 4 comments 0 points Most recent by Matt Foss 07/08/20

First post 07/06/20,Last post 07/08/20
I've been able to find close approximations to what I'm looking for, but have one missing piece. I have a summary sheet ("summary") that pulls data from another source form evaluation sheet ("source"). Essentially, I ha…
Formulas and Functions164
Matt Foss
Question on auto-population between sheetsQuestion

Answered - Pending Review Jonathan Kearns 17 views 3 comments 0 points Most recent by Paul Newcome 07/08/20

First post 06/30/20,Last post 07/08/20
I am trying to setup some sheet automation as follows: Program Manager sheet: Tracking ID is auto-generated using auto-numbering When a new tracking ID is generated would like to populate that new ID into another Master worksheet into next…
Formulas and Functions173
Jonathan Kearns
Stumped on formulaQuestion

Answered - Pending Review dskpetersen 8 views 1 comment 0 points Most recent by Paul Newcome 07/08/20

First post 07/07/20,Last post 07/08/20
I need a formula that I'm not sure I can do. I have tried many different things, and only have a portion working. I need to be able to determine the "difference" between our $$ bid and either the low bidder, if we are not, or t…
Formulas and Functions81
dskpetersen
Simple If with @row - Why won't this work?Question

Answered - Pending Review Doyle54 27 views 6 comments 0 points Most recent by Andrée Starå 07/08/20

First post 06/25/20,Last post 07/08/20
(Image)
Formulas and Functions276
Doyle54
Lookup / Return Cell (contents)Question

Answered - Pending Review Susan Swisher 22 views 2 comments 0 points Most recent by Genevieve P 07/08/20

First post 07/07/20,Last post 07/08/20
I need a formula to look at @column1/cell (whatever it is); go into another sheet, find that match/@column1/cell, and then return what is in another column (in the 2nd sheet). Hopefully, that isn't confusing............
Formulas and Functions222
Susan Swisher
How do I use the INDEX & MATCH function when trying to pull information from one sheet into another?Accepted Answer

Accepted Answer Ameena Bossier 37 views 3 comments 0 points Most recent by Andrée Starå 07/08/20

First post 06/25/20,Last post 07/08/20
Hey All, Reaching out to the masses to hopefully get some help. I need to pull information from sheet 1 into sheet 2 using the INDEX and MATCH functions. Sheet 1 that I want to pull information from has: Child rows Task Name column (The …
Formulas and Functions373
Ameena Bossier
Hello - need help! :) Summing data from multiple sheets.Question

Answered - Pending Review Tammy Parker 15 views 3 comments 0 points Most recent by Andrée Starå 07/08/20

First post 07/07/20,Last post 07/08/20
Hello I have 5 sheets of many cells of data for different work groups within my organization. I need to create ONE summary sheet that totals each of the cells from the 5 sheets. The only way I can figure out how to do it is by creating th…
Formulas and Functions153
Tammy Parker
HAS Functions Returning 0Accepted Answer

Accepted Answer Syed Mohd Muhafzal 12 views 4 comments 0 points Most recent by Andrée Starå 07/08/20

First post 07/02/20,Last post 07/08/20
Hi Smartsheet Users, I am struggling with the formula HAS with multiple selection drop down values and hoping I could get some guidance here. Here, i would like to count how many "swimming" classes I have in a week. (Image) The…
Formulas and Functions124
Syed Mohd Muhafzal
Drop Down ListAccepted Answer

Accepted Answer Rob Myer 14 views 5 comments 0 points Most recent by Andrée Starå 07/08/20

First post 07/03/20,Last post 07/08/20
Q: Is it possible to assign a drop-down list to a single cell and not the entire column?
Formulas and Functions145
Rob Myer
Formula to count of items in a multi dropdown listAccepted Answer

Accepted Answer lcamacho19911 5.3K views 30 comments 0 points Most recent by Genevieve P 07/08/20

First post 10/03/19,Last post 07/08/20
Hi The drop-down multi select is very nice and useful. I started to use it yesterday, but I need to use the formula =countif(range, criterion). I want to count how many times a value is repeated in a range that has multi values. I use …
Formulas and Functions531730
lcamacho19911
Conditionally changing cellsQuestion

Answered - Pending Review Scott Rae 53 views 10 comments 0 points Most recent by Andrée Starå 07/08/20

First post 01/31/20,Last post 07/08/20
Hi, I'm looking to see if there is any formula(or anything else) I could use to change a cell to a specific pre-defined value, if a different cell is CHANGED TO a certain value. I know that the changed to functionality is available through…
Formulas and Functions5310
Scott Rae
Can I verify each entry in a multi-contact list exists in a different multi-contact list?Question

Answered - Pending Review Ryan Michalski 23 views 3 comments 0 points Most recent by Genevieve P 07/08/20

First post 06/30/20,Last post 07/08/20
I use Dynamic View. Dynamic View, unfortunately, only allows 1 single column to be the list of users who can see data in a specific row. Smartsheet is not able to combine multi-contact lists into a single column that is recognized within…
Formulas and Functions233
Ryan Michalski
Formulas on formsQuestion

Answered - Pending Review Joachim Chenna 17 views 3 comments 1 point Most recent by Andrée Starå 07/08/20

First post 07/07/20,Last post 07/08/20
Hello, I have question do you know how I can use the same "formulas" for each new ligne generate by the form ? The formulas has the purpose to take information from another cell. MT for your help. I tried this tutorial but it d…
Formulas and Functions173
Joachim Chenna
Allocate hours into months based on given date rangeQuestion

Answered - Pending Review UrbanNomad 27 views 3 comments 0 points Most recent by Ben G 07/07/20

First post 06/25/20,Last post 07/07/20
Hi All, I want to allocate hours equally based on a given date range, in excel I could get it work using Eomonth, in Smartsheet, I'm struggling to achieve. I have attached the image of the formula I got it working in Excel, how do I replic…
Formulas and Functions273
UrbanNomad
CountIfs Dates with ISBLANK ContinuationQuestion

Answered - Pending Review Rigoberto Huizar 25 views 2 comments 0 points Most recent by Rigoberto Huizar 07/07/20

First post 07/06/20,Last post 07/07/20
I am having trouble with my formula. I'm a newbie and my logic be flawed and terrible. Learning in progress. I thought I had received the answer but as it turns out I was still trying to acquire more information. Essentially what I want …
Formulas and Functions252
Rigoberto Huizar
How to create a function that automatically updates to the newest row createdAccepted Answer

Accepted Answer Gabriel Perez-Pujols 22 views 3 comments 0 points Most recent by Paul Newcome 07/07/20

First post 07/07/20,Last post 07/07/20
Hello! I am relatively new to Smartsheet and I am still in the process of learning excel. One of my sheets is a simple data entry sheet in which I need the Sheet Summary to show the information in the most recent row. I have been using the…
Formulas and Functions223
Gabriel Perez-Pujols

Formulas and Functions

Formulas and Functions

Discussion List