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!

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

Watch video

Trending in Formulas and Functions

Help with converting inherited Excel Formula
Hello, I have inherited this formula from an excel sheet that i can trying to get working in Smartsheet however as a novice... i am struggling. Grateful for any support. =IF(M10<=5,"Low",IF(AND(M10>=6,M10<=11),"Medium",IF(AND(M10>=15,M10<=25),"High",IF(AND(M10=12,K10=4),"High","Medium")))) Protonspounge
Help with a Harvey Ball formula
Hello everyone who's smarter than me: I need a formula to result in the following: Field empty if no start date listed Green ball if complete, regardless of start date Yellow ball if not complete and start date is within the next week Red ball if not complete and start date is in the past Gray ball if start date is more…
Revise Formula - RYG
I am using this formula: =IF(OR(CONTAINS("Insert Word", [Column 2]@row), [Column 1]@row = 1), "Green", "Red") However, I need to revise it so that when column 3 (which is an additional column not mentioned in the original formula) contains a specific word, it changes my ball to yellow.

Formulas and Functions

Discussion List

Help Article Resources