Formulas and Functions

Archives for February 2021

I need a formula for a report
Need a count if formula and sum if for to look at certain sheet and populate another sheet: EX. If I have a row that has a cell with certain name like Event1 then has appt set checkbox and sales amount I want to count how many appointment set checkboxes there are with the name Event1 Also same example Sum a Sales amount if…
COUNTIFS FORMULA with Cross Reference and MONTH Date
I would be most appreciative for help on this formula. I am cross referencing data from another sheet and trying to count how many times TM & AM pop up in the month of February. I keep getting the UNPARSEABLE error. =COUNTIFS({GCS Service Lines},("TM & AM", @cell)>0, MONTH,{Date Created}(@cell)=2) Thanks in advance for…
How can i have a cell that shows an amount from another cell if its 30days past a date in another ce
row 1 date billed row 2 dollar amount row 3 dollar amount past 30 days Row 4 dollar past 60 days Row 5 Dollar past 90 days =if( row 1 is less then 30 days) show row 2 =if( row 1 is less then 60 days but greater then 30 days) show row 2 =if( row 1 is less then 90 days but greater then 60 days) show row 2 =if( row 1 is…
Cross Sheet Reference Formula
I am trying to write a cross reference sheet formula to count MTD dates in a column using this formula I found here but it is not working. Thoughts? =COUNTIFS({Andee Metrics Terms}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0 = MONTH(TODAY()))))
Excel Form. works well but not in Smartsheet =COUNTIFS(Q:Q,"<>*/*/*",Q:Q,"<>",Q:Q,"<>Complete")"
formula like MAX, but that grabs the top three?
I have a sheet that collects project updates per project, and I want to surface the most recent three updates per project for a report. So the data is really simple, there is a date and an incrementing autonumber, so I can use either of those for the metric. Table has these fields, so pretty basic stuff: ProjectName;…
Trying to get a list of distinct values across multiple columns
Hello, I have a list of providers. Each provider can list up to 4 languages spoken at their office. I would like to get a list of distinct languages for the entire list of providers. Language 3 and Language 4 have different languages further down the rows. I have joined the columns but I still cant get a list of distinct…
SumIF Cross Sheet
I am simply trying to summarize AR data for a dashboard on a summary sheet. I need to know the balance due for each company totaled up. Below is one of many attempts I have tried. There are only six options so I canmanually update the formaula for each company but that didnt work either. What am I messing up? Thank You
Set the Current Date with Record a Date Action
How can I set the automation so that it only records a date in the row that is being updated? At the moment it is updating every row in the sheet when a single row is being updated. https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action
Status Roll Up Formula
I am working on a sheet to track the status of test cases and want to have the status of the subtask roll up to the parent status. The goal is that if 5 tasks are available and 2 "Passed", 1 "In Progress", 1 "Not Started" and 1 "Failed that it shows the status of "In Progress on the parent row. Below is the formula I am…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

COUNTIF with AND
I've written the statement but am getting a #INCORRECT ARGUMENT SET error. I'm new to SS, any help is appreciated. =COUNTIF(AND([Opportunity Risk Path]:[Opportunity Risk Path], [Module Approval Status]:[Module Approval Status], ="Conventional", "Not Started"))
IF CONTAINS Formula to return different text values in second column
Hi! I am looking for some help in creating a formula where if a certain word is found in one column, another word is returned in a second column. For example if "Head", "Nasal", or "Spine" is contained in the Procedure column, "Neuro" is returned in the Clinical Specialty column. I've tried different variations of this and…
I need to result the column's name when using a JOIN(COLLECT) or INDEX(MATCH) formula
Previously, Row 1 of my smartsheet contained the column's name. This way, I could write a JOIN(COLLECT) formula to return the column's name if certain conditions were met throughout the sheet for that row. That worked until we applied column formulas to some of the rows. Now Row 1 can't be the column's name because of the…

Formulas and Functions

Discussion List

Help Article Resources