Formulas and Functions

Archives for September 2022

CountIF not returning expected value
Using an Countifs to look at a column called Account and count if not blank. The formula using is =COUNTIF({EMEAR Tiger Team Exec Engagements H2 Range 2}, "") . Expected count is 38 and returns 12. The sheet being referenced is: https://app.smartsheet.com/sheets/59wgCjQqwPQ6jrFxH9RfVM6gpC2gQ93JP97GGhF1?view=grid The metric…
Sheet Summary formula to pull value of cell based on latest date
Hello Community - I've scoured the forum and can't find a solution for my particular situation. I have a sheet where I want a summary field to pull in the value in the CL Readmit VAR column based on the most recent date in the Modified column. The sheet will continue to populate rows from a form so I need the summary field…
Comparing two columns
Hi Everyone, I have created two drop down columns. So for example purposes: lets say column A contains 5 grocery items (bread, milk, butter, potatoes, chicken) and those are my mandatory items. Drop down with multiple entries Columb b has the same 5 things - but when we take stock we only have 4 grocery items (milk,…
Automation for creating Sub row in same sheet
How can you setup an automation to create a subrow for a row once a condition is met? (such as a checkbox is marked in a row, and that triggers the creation of a sub row to that now parent row)
My COUNTM formula will not stay
Not sure what to do here. I inherited this sheet and the countm formula will not stay. If you remove the apostrophe it reverts back within a minute. How can I keep the formula?
Shortcut for nested IF function
I am tying columns together in a sheet so that when the user selects a product from a dropdown from a cell, it will automatically populate the product's manufacturer. I can do this using the following: =IF([Product Description]@row = "Product 1", "Company 1", IF([Product Description]@row = "Product 2", "Company 2",…
Generate Document - Text Shrinking in Field set to Multi-line
Hi, Bit of an odd problem!! When generating a document, text in a field shows as one line and the text shrinks. The pdf form field is set as Multi-line. What I have found is when the field is the only one mapped in the document generator, the text display as Multi-line. The moment I map others the text goes to one line and…
Ancestor 2 to autofill with Ancestor 0 value in primary column
I am in the process of creating an assignment sheet for staff. There are a few goals in the sheet. Card View by associated projects (ideally be able to move them/reassign them with ease) Card View by staff member Grid View by department. Staff to add percentage of time projected to spend per project separated by cost…
Modified Date
Looking for some assistance...so I am using the Modified Date column in one of my sheets and then using that to identify non-updated/aged rows however there is something in my sheet that is resetting or refreshing each of the rows on a daily basis even when I know there hasn't been a manual update to all the rows...could…
Countifs when there are different answer options
Hello, I want to collect data on the number of times certain machines are reported between certain dates Here's is the basic formula that works. =COUNTIFS({NS Repair Detail Report Type}, "OOB", {NS Repair Detail Machine Model and Description}, "Machine Name 1", {NS Repair Detail Activity Date}, >={Sept 1 2022}, {NS Repair…

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

Visual Status Alert
Hello All! I hope this is an easy soulution. Can anyone assist me with creating at status alert based off of today's date and a start date? I want the Delay Status to automatically put the status listed and color the cell. If a Cleard to Start has a check mark, then it shows no delays and color coded green. At some point,…
Exact
I just want to compare 2 cells (=exact in excel) if they are exact then true, if not then false, why am I struggling
Finding the oldest date on multiple sheets and also based on text in another cell
I have 2021, 2022, 2023, 2024, and 2025 sheets with different departments' requests for action (RFAs). I am looking for a formula to show me the oldest open RFA for each department. Some departments will be in 2021, but some will be in 2024. A sheet summary formula is not what I am looking for. I need a reference sheet…

Formulas and Functions

Discussion List

Help Article Resources