Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Linking the same date from one cell to a second cell on same sheet
Hi Gang, I am having a hard time trying to use the date entered in one cell to another cell in the same row from the same sheet. The column I want as the target is RECEIPT DATE and the column for the duplicate date is START DATE. I have tried =[RECEIPT DATE]@Row but it didn't return anything at all. Thanks for the support,…
Is there a limit to adding VLOOKUPs?
I am working to sum multiple columns that match a specific criteria. I was able to accomplish this with the SUM formula and using VLOOKUP. It works when I add 2 VLOOKUP formulas together but if I add more, it says #NO MATCH. Is there a limit? Example: =SUM(VLOOKUP(Description@row, {Unit 3}, 2, false) +…
Hi all, I would like to set a formula to join data 1,2,3 but something is wrong as below picture.
Could you help me out pls? the main purpose is to join data from upper 1-3 without "/" Redundant in case there is no data at any cell among upper 1, 2, 3.
How to copy the cell values from diffrent workspace sheet into single sheet (Automate the process)
Hi Team, quick help needed. for example i have 10 project (each is a workspace), each project have 1 sheet with multiple columns and i need to fetch information of 4 columns from this sheet AND then put it on the separate sheet (can call it as master sheet) has information on project level i.e each row is a project in this…
IF statement using AND to generate colored symbol
Hello! Can somebody please help with the following formula? I am trying to generate the colored symbols (Green,Yellow,Red) based on the percentage of tasks complete along with the go live date. I am able to get a single If statement to generate the symbol but am failing at the AND one. Below is the formula that I'm using…
Formula for Lights
Hi all, I have a sheet that contains fields for "Preparer Due Date" and "Preparer Status" and am trying to add in the following logic to a formula: If Preparer Status is "Complete" OR Preparer Due Date is earlier than today AND Preparer Status is NOT "Complete", then put a green circle If Preparer Status has a status of…
JOIN(COLLECT 2 CONTAINS criteria with multiple returns Cross Sheet
Hello! I'm looking to build a JOIN(COLLECT that will allow me to pull email addresses from a contact sheet based on two criteria. Both criteria have to be CONTAINS. They will be the Department (Dept) and the Job Title (Role). I frequently send out emails based on department, but also specifically to the managers of those…
creating formulas for column and rows linking to other sheets
I could not find what I was looking for in other questions so I will try to pose what I need help with here. I have one sheet that I have columns and rows summing totals from another sheet ... so totals by the week and the location. I now want to create a 7 day average on an existing sheet that I was doing a 5 day average.…
COUNTIFS - trying to make a formula that does the same work as the "Has Any of" filter from Reports
Im very new to writing Smartsheet formulas and have been scouring these forums the last few days to try and figure out how to get this to work I am trying to write a formula that counts the rows with the following column criteria. Here is what I have so far but its not working =COUNTIFS({Module}, OR(@cell = "AMBFO", @cell…
Checkmark Column Formula
Afternoon, I have tried every formula in the book to count all check marked boxes within a column. Can anyone just write the formula that works for them? This is the formula I am trying to make work- =COUNTIFS([Construction Complete- Not Submitted]2:[Construction Complete- Not Submitted]254, 1)

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Formula to Total rows
I have Rows with names of People Then I have columns that show projects / Activity names I am trying to create a summary field that will sum all the time spent on two categories by all the people. Either "Customer Meeting" or "Testing". The rows those are listed in might not always be the same. For example one month they…
Reporting to Dashboard issue
I have my main metric that rolls into a report that is using monies ($) and it worked perfect till this morning. I have checked and confirmed that every cell is in the $ format and yet it is still not showing up anymore on the dashboard as $. There are no blank cells in these columns either. I can not for the life of me…
Unique IDs that combine a field and sequentially increase
I'm creating a change management log that allows users to submit change orders via a form. To simplify this question, I would like the user to include the project number in their request (this will populate a sheet column titled "Project Number"), then a formula in the sheet (in another column titled "Change ID") will…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags