Formulas and Functions

Archives for December 2022

Highlight Today's Date on a Report
Hello- is there a way to highlight today's date in a report without having to go back to the source pages? I would like to have our Go Live board to have "today's" date highlighted while also having the rest of the dates for the on the report, so it helps our implementations and field team plan.
How to sum total meeting hours members of a project will have to attend?
I have a Smartsheet that contains the meeting name, the number of hours the meeting will be conducted, and a contact list of the attendees of each meeting for a project. I would like to create a formula that will sum the total meeting hours each member of the project team will be expected to attend. For example, the…
If checkbox is checked, add certain fields to a destination sheet
Hi there! I am hoping for a formula/solution for a cross sheet formula. If a checked box is checked on the source file, I want to add info in 8 columns to a destination sheet. I have two software request forms (and I need to have 2 forms) however 8 columns have duplicate information. Instead of having users file out the…
How do I exclude values from a COUNTIFS expression?
Hello All, I am hoping someone can help me figure out - how do I get my formula to exclude specific status items from its count? I am pretty new to formulas - I would like my formula to count the number of statuses by type - and exclude any that are 'Cancelled' or 'Complete' Here is what I have so far - I tried various…
Can we use JOIN with an IF Statement
Hi The scenario is I run a meeting with tasks for an agenda item and we have a list of them. Each task has a reference to the Agenda Item. eg. Agenda Item 1 - Action 1 - update plan Agenda Item 1 - Action 2 - distribute plan Agenda Item 2 - Action 1 - create resourcing requirements Agenda Item 2 - Action 2 - provide…
Only count items that aren't marked "complete" in the status column.
Hello All, I am hoping someone might be able to help me w/ my formula? I'd like it to only count items that aren't marked "complete" in the status column. This is what I have so far: =COUNTIFS({Remediations Category}, "NEC VIOLATION - ARC FLASH FAILURE", {Remediations Core OPCO}, "NSP", {Remediations Year}, "2022",…
Subtraction Issue when there is no input
Hello, I have two separate formulae to collect data based off of dates and input, I need a further formula to subtract one from the other, but I am struggling to get it to work. Subtracting them works fine, Cell v Cell, until there is no data input. I thought perhaps ISNUMBER might be a strong way to go but hit an…
Index Collect value based on a date
I want to index a value if the "This Sheet Date" on my final result sheet is greater than "Date Value Effective" on my database sheet. Screenshots should help. I have a column showing the value that I want it to return. It's showing me the 2% because technically all of the "This Sheet Dates" are greater than the "Date…
Error message when trying to cell link
I have created a comprehensive report where I am trying to cell link information from 5 different sheets. I am getting this error message.. Is there a limit to how man cell links you can have? Is it because I am using the cell link in a formula? I have a lot more referencing to do and I am getting this message. Please help!
AVG(COLLECT with Multiple Criteria
Hi, I am trying to calculate the Average for the "Days Open" column using multiple criteria. Currently I am using two different formulas (below) to try and come up with one number. It would be great to use one formula instead of two. Your help is much appreciated! =AVG(COLLECT({Master Case List Days Open}, {Open/Closed},…

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

Formula Issue
Hi all, I am trying to average the values in a column, 'Defective Quantity', when the value in column 'hide' is an '11' (for month) AND the data in column 'Year' is 2023. =AVERAGEIF([Defective Quantity]:[Defective Quantity], hide:hide, =11, year:year, =2023) I am receiving an #UNPARSEABLE error. Any idea what I'm doing…
Marrying three formulas together
I have three different formulas, containing various criteria, which I want to want to marry together and for which all things must be true: 1) project start date is less than 1/1/24; 2) go-live date is greater than 12/31/23 and 3) the project stage is "In progress" or "Waiting to start": =COUNTIF({HR Projects Project Start…
Automatic Parent by Child Status
Hi everyone, I'm working on a formula in my status column so that the parent line will automatically update based on what the child/children are. Here's the current formula I have that's not quite working: =IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Achieved") =…

Formulas and Functions

Discussion List

Help Article Resources