Formulas and Functions

Archives for January 2022

How can I count the number of times a number appears in a column?
How do I count the number of times a number appears in a column? Is it a countif formula?
Index Match Formula Using Multiple Sheets
I'm trying to write an index match formula for a downstream transaction sheet in which if payment date is not found on sheet 1 it would be pulled from sheet 2. I tried the below IF INDEX MATCH formula but got an #INVALID REF error. =IF(INDEX({Payment Date}, MATCH(Invoice Number, {Invoice Number }, 0)) = 1, INDEX({Payment…
Need help with formula that shows both years and months
Hi, I need help with setting up column formulae for the two (yellow highlighted) columns. I have included the sum fields for each of the columns. Please suggest a formula so that one may see both the years and months of the grant in the column/ cell. For example for the given start and end dates, the Original Length of…
Cross Sheet Formula Needing to return a value looking at a table
I am trying to look at a table of capacity percentages by week # and by team member. The table has a list of names down the Left Hand Side and the week number in a row along the top (see picture below). I need to return a value based on the team member and the week # in a metrics sheet that I that will have the persons…
Change a cell value If a certain Postal/Zip Code is entered, is it possible?
Hi there helpful people! I am using smartsheet to track jobs in order to then export a monthly report. I have approx 2000 postal/zip codes attributed to client sites within a particular State - and what I'm hoping to do is set up an automation (either within a workflow or a formula) where if a particular postal/zip code is…
Return date check box checked
I am trying to determine the actual date a check box is checked to compare to a target date. Is there a way to return a static date triggered by the completed check column?
Countifs
I have a column that has 3 different entries for each row and I need to count each of them in the column. How do I use Countifs for this task? I keep getting either Unparsonable or Incorrect Argument Set =COUNTIFS([Event Type]6:[Event Type]941, "Tag 1", [Event Type]6:[Event Type]941, "Tag 2", [Event Type]6:[Event Type]941,…
Help with Sumifs
Hello. Hope everyone is well. I'm having issues with a =SUMIF OR =SUMIFS formula =SUMIFS({Fed Admin FTE Usage}, >0, {Employment Status}, "On-Board") I'm getting invalid operation. Please help.
Formula to add a # of days to a date to calculate ETA
I have a form that calculates a TAT based on the amount of material requested (1-2 items is 4 days, 2-5 is 6 days, etc etc). I also have a column that is a date only version of the time stamp, so the requester does not have to enter "Date of Request" inthe form, it defaults to the date of the request this way. What I want…
Filter Option to restrict users to not see tasks for other users
Hi, I created a "Shared Current User" as a shared filter and applied that filter while extending access to the assigned users so that they can only view their tasks and edit the sheet. How do I lock or restrict this shared filter in such a way that users cannot un-filter the sheet or create new filters. Basically, restrict…

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

Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…
How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…

Formulas and Functions

Discussion List

Help Article Resources