Formulas and Functions

Archives for July 2021

VLOOKUP for multiple values
Hello! I'm using JOIN COLLECT to look up a variable's name in a block of text. However, I am also trying to use a VLOOKUP to change the found variable name in the JOIN COLLECT column to the standardized name I need it to be for other calculations. For instance, the JOIN COLLECT finds "Annual Miles" but I want it to be…
extract name from email address
Hi there, I have a created by column that captures the email address : nero.naidoo@angloamerican.com I want to create a formula that will create a name column that returns Nero Naidoo. I've tried some of the split-to-text formulas suggested and seem to only come up with nero.naidoo. I want to capitalize the "N" and replace…
Automation triggered by INDEX / MATCH cell value changes
Howdy - I feel like I've tested this every which way but didn't want to throw in the towel until I asked the community. Is there any way to run an automation (specifically "Copy rows to another sheet") based off a changed value via column formula INDEX / MATCH? My goal here is two fold: limit the number of columns added…
Work due in the next 7 days
Where am I going wrong with this formula? I want to see tasks that have a {Need by Date} of TODAY through the next 7 Days and also filter out any Assignee's {Task Range 1} that say "Schedule Milestone"? Thanks for everyones support on this one :) Both of the datasets would both be pulling from another sheet.…
Custom Number Formats?
Rather than showing a 0 in a cell, I'd like it be be blank or use the " - " symbol. In excel I use custom number formats to accomplish this. First, I tried conditional formatting to a white (invisible) font, but it doesn't look like i can limit the rule to certain cells (only an entire row or column) so that won't work if…
COUNTIFS and AND
Hi there! I am trying to insert a formula that reads two columns from another sheet. I want to count the cells if one column equals the Primary column name in my new sheet and the other does NOT equal the status "Scheduling Complete". I can't figure it out! Below is what I currently have and have tried dozens of…
Using Boolean Operators in Countif with Dates
Hello. I'm not sure why my formula isn't working. I'm guessing the issue is my syntax but I don't see where it is wrong, if that is the issue. I'm trying to get countif to count the number of rows with a date greater than 1/1/20. The formula does return a value but that value is 0. Here is the formula in case it's hard to…
Cross-Sheet SUMIFS is returning #UNPARSEABLE
Hello, I'm having an issue with a cross sheet formula. I'm trying to build a budget tracking system in two parts. It involves a top sheet with a the sum of all expenses by category and an expense log that is a running list of expenses. I tested the following SUMIFS in the expense log and it works correctly.Where it is…
Time duration
Hello how can I get the number of hours to calculate with a Start Time and and End Time? Want the AM PM format as shown. Thanks for your help!
Cross-Sheet Formula SUMIFS returning #UNPARSEABLE
Hello! I'm running into a weird error with a Summary Budget Top Sheet I'm trying to build. I have two sheets. One summary budget top sheet and one expense log sheet. The summary sheet lists all the expenses types available on the expense log and I am trying to create SUMIFS to group together specific types of expenses on…

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

How to Count how many times a cell is in a column
I am struggling with a formula how many times Delta appears in the Client Column. The formula I am using is: =COUNTIF((Client:Client), FIND("Delta", Client:Client, >0))
How to reference column name pulled from formula
I'm trying to pull a date from columns based on the number in the helper column. I know I can do this with nested IFs, but is there a way to change which column I'm pulling the value from? My column Start Date should be whatever the column is based on the Index. So for the first column below, since Index is 3, it should…
Conditional Formatting Based on 2 Date Columns, with or without Helper Column
Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06/29/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06/29/2023 deadline,…

Formulas and Functions

Discussion List

Help Article Resources