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!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

Watch video

Trending in Formulas and Functions

Updates to a sheet via form
I've seen several posts about updates to a sheet via a form or request an update via trigger. Here is my task... Create a sheet to track mileage for a list of team member for reimbursement. Information on the sheet needs to stay confidential. I need to update three different cells on a team members record on the sheet.…
Sum Formula containing keywords?
I'm trying to keep a running sum of the amount of liquid we are holding for our business's transfer log and I am not even sure where to begin! We have to log transfers (IN) and (OUT) and I would like to write a formula to keep up with this if possible. (Adding the amount in rows with IN and subtracting the amount in rows…
Could someone help me with this formula?
We are asking our managers to use SmartSheet to enter information about their employees (via the add form). Be are requiring them to enter the employee ID which has to be 9 digits, but many times they don't enter all the numbers. I read about helper columns to check whether or not the format is accurate and then send an…

Formulas and Functions

Discussion List

Help Article Resources