Formulas and Functions

Archives for September 2023

Looking to track cleanings
Currently I'm looking to add a validation mechanism to a sheet and would like to have a systems that reports after (X number of) cleanings have been completed for the day. This would add a row displaying the room that was complete for the day. If the room was not complete it would show up in red as not complete. I'd also…
Convert to Column Formula Error
I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = "Complete", [Status]1 = ""), "", "Yes") Two issues, though: 1) The [Status]1 brackets are being automatically removed and reformatted as Status1 2) When I try to convert to a column…
Counting yes and yes w/notes
Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called "Yes w/notes". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = "Yes") / COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…
Combine 2 formulas with an or function
Hi all, I have 2 working formulas that I want to merge with an or function. Either formula 1 or formula 2. Formula 1 has Green as result if first 9 chexboxes are checked,gives red if no box is checked, has yellow if some boxes are checked. Formula 2 gives blue as the result if all boxes have been checked and has yellow if…
IndexCollect / JoinCollect for Multi Select Columns
I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns. I have worked out a formula for PRODUCTS OTHER…
Help with Syntax
I'm trying to get the sort order to group based on the "highlights", "lowlights", "risks", and "issues" but my only options are ascending or descending. I need a custom sort order... So I'm trying to hack it using a hidden column that uses numbers but it's showing up on the grouping when I use grouping... And I'm…
Dashboard Question
What is the best option for showing a list of "Highlights", "Lowlights", "Risks" and "Issues" on a dashboard report? In other words, it's commentary about the project. I'm going down the path of creating a sheet with the updates that I'd like to publish to the dashboard. From there, I've created a report that pulls the…
Formula help
I have two formuals I need help with. Can we get a formula if the status is sold but the "Closed to Builder" is not checked?? And 2. Formula that would calculate how many cells have nothing in the "Status" column? Please and thank you!
How can I calculate the average of the values over a period of time?
I have two databases, one with the values and dates, and the second to create a more succinct report with just the average of the values in database 1 for each month of the year, but I can't find a formula to help me with this. Does anyone have a solution for a similar case? I need the calculated average to be like…
Formula error
I want to create a sheet summary of some data. One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked. One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked.

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

Record a date automation isn't working
I set up the automation below, and it's not working. When I change the status of a row to complete, nothing happens. If I go into the automation manager and select "run" or "test" the completion date column fills in with today's date. What am I doing wrong? This should be straight-forward. I've tried building this…
Identifying oldest entry on sheet, for different groups
Hello! I am working on a "waitlist" process, where the sheet will have multiple candidates under each work location. I need a formula to identify the oldest entry for each location, and if there are multiple entries for the earliest date, use a "score" column identify the higher score as the next candidate in line. Example…
Week Over Week Change
I have a sheet that has orders entries from the last 6 months. This sheet continues to populate as more orders are made. This is just my repository sheet. I am looking to get the week over week change metrics for the last 7 days. Here's the formula that can provide me that week over week change: Week over week %% =…

Formulas and Functions

Discussion List

Help Article Resources