Formulas and Functions

Archives for December 2022

If/Index/Match help!!
Hi! I am relatively new to Smartsheet. Usually I can poke along and work through to get the result. Today, not so much. I am trying to pull data based on data input. With the data, I need to match and select from one of 3 columns on another sheet. I have attached a couple screenshots. User selects accumulation from the…
What is the best way to count a range of cells part of a hierarchy that meet a specific criteria?
I am trying to wright a formula using the descendants function and I am sure at this point I am over complicating a rather simple process but need some assistance to see past my own frustrations. In the picture below, the Blue row is the parent row. I need to count the descendants for that parent that meet the criteria of…
Assigning agent to a row in "round robin"
"I am using: "=IF"([Row ID]@row <> "", INDEX({People Sheet Range 1}, MATCH(MOD([Row ID]@row, MAX({People Sheet Range 1})) + 1, {People Sheet Range 1}, 0))) on my primary sheet to return an agent name in sequence from the secondary {people sheet}. I can't get the formula to return the name, I just get the sequence number.…
INDEX COLLECT NAMES FROM 2 SHEETS TO ANOTHER SHEET
I HAVE (SHEET A) AND I NEED TO RETURN NAMES WHEN A MATCH OF IP ADDRESSES FROM SHEETS (DEL 1) AND (DEL 2) IS MADE.
Difference between already paid and total paid
Hey there, is there a way to automatically calculate the difference between already paid total to pay amount when status is "payment planned" or "invest planned" or is it better to have another column "already paid" and to build a formula there that calculates "total amount (frequency)" x frequency since start of payment ?
Sum dollars based on names in multiple columns
I have a shared tips sheet. A big tip is divided by the coaches that are coaching for the group. If John is coach 1, Dave is coach 2, and Skye is coach 3, then the $30 tip would be split $10 each. The next group may have Skye , Mike, and Nate and may have a $60 tip. Etc. I am trying to write a formula that says of Skye is…
SUMIF with two or more criteria
Hi, I use this formula =SUMIF({Rechnungserfassung Zahlungsstatus}; "Investition geplant"; {Total (Brutto jährlich)}) 1) I need to add another criteria f.e. "Zahlung geplant". 2) What if I have various +criteria and want to exclude another one f.e. "exclude payment" How do I have to change the formula in order to get the…
Looking for solution to "Parent vs Ancestors" problem
Hello, I have been having tons of trouble coming up with a formula that will give me the desired result. The goal, it to populate a column with two pieces of information from other columns. In the attached image, the column "project name and header" is supposed to take the ancestor of SLS Job Number and the ancestor of…
I am using the vacation planner template and have a problem with calendar view
As soon as I add a second vacation scheduled in Table View, the previous vacations are no longer displayed in Calendar view. I would like to see all planned vacations in Calendar view. What should I do?
Nested IF Statements
I'm stumped on a formula and I feel like it's probably an easy fix I'm just not seeing. I have a sheet with various date columns, and I want the formula to check to see if that date is within 180 days of today, and if it is, I want it to return Alert. However, the date isn't populated for every row, so if it's blank I want…

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

RGY Button Edit Needed
How do I edit this formula to turn button yellow when due date is 5 days away. =IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green"))) Thanks!!! ~ Carla
Formula Help Countifs Percentages
I need a formula to calculate sets of specific Date columns, and tally those date columns into a % of that set? For e.g. I have 2 groups. Each group has specific columns that make up the set for each group. There is a unique separate column that will tally the IMP Dates entered (specific to the Group). I need a formula in…
how to collect a date within a sheet?
Hi, in the image below I have in my "extrusion" column an entry that populates by a formula (in this case "M3406 HEAD TRACK 15' is populating) I'm looking to populate the "Last Cycle Count Date" column by searching the "last cycle count date" from rows 1:49 looking for a date that has the "extrusion column from rows 1:49…

Formulas and Functions

Discussion List

Help Article Resources