Formulas and Functions

Archives for March 2018

SUMIFS Formula Needed for Multiple Calculations Summing from the Same Column
I am working on a budget sheet, and need to be able to calculate OPEX expenses per site, and CAPEX expenses per site. I've got 6 different sites, and need these totals for each quarter. The formula below seems to work, but I can't use it more than once within the same column without getting a circular reference error. This…
How do I - formula ques
I have a formula =COUNTIF(Age8, >65) which is working, but I want it to only give me a count if a certain checkbox field is checked. Is there a formula for this?
UPPER formulas
Is there a way to set a formula so all text in the column or sheet is upper case even if someone enters it on a form in lower or title case?
Countif with Checkboxes
I need help doing a countif with checkboxes. I have a formula I need to countif in column Project if it has a value of Yes and column Parent Task is check. I also need it to countif column Project also has a value of partial. This formula worked until I tried to add the Parent Task column equaling 1. Anybody know what I am…
Issues with the new cross sheet formulas?
My totals were working finally on a summary sheet but then all the columns started changing to the same formula. I tried twice to go in and reset the reference sheet range but then it would just everything on the sheet. Anyone else have this issue?
Is there a way to NOT auto number the 1st Row? ...and other SUM row questions.
I am using the first row for totals and it is throwing my auto numbering system off. Is there a way to have the auto number start on row 2? Alternatively, if I put my totals on the last row, the web form will populate new rows below my totals. What are some best practices to display my column totals on a continuously…
Checkbox question
Is there a way to have a checkbox automatically checked if other columns with checkboxes get checked? I have 4 columns with checkboxes and instead of doing value columns for each one I would like to do a summary checkbox and just value one column. If that's not possible, can you set values for more than one column?
What would the formula be for.....
I need to sum or count the number of people in my sheet that are under 17 and over 65, based on who is checked. Can I do this using a cross sheet formula so all my totals are together? I have both DOB and age columns - can the formula be on the age column or do I need a value column to set age parameters?
Cross sheet formula - help
With this new feature I'm hoping I can eliminate abut 10 columns from one sheet and create a sheet just for totals. Currently I have several columns just for counting the value of columns with checkboxes. Do I have to leave these in the same sheet? When I tried to do the values in a new sheet by referencing the main sheet…
=If Formulas
Hi! I am attempting to create an "=If" formula that has 21 "if_then" statements. When I get to the end my formula enters as "Unparsable." Is there a limit to criteria in a formula? Thanks!

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Trying to add a year to this formula
So here is my current formula… =COUNTIFS({Date Completed}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Staff name}, =Staff@row ) This works. It gives me a number for all observations submitted in the month. The problem is it also counts the month for the previous year. How do i filter the count by month and year? Thank…
Recording words from Column B to Column A
Hi, I'm writing to write a formula that will record certain words that appear in column B into column A. Column B is a dropdown menu with multiple options available. I only want to record the words RED, BLUE, PURPLE, if they appear in column B and if none are present, I want the cell to remain blank. I'm struggling to know…
Extract 4th email address from multi select cell with CHAR(10) as delimeter
I have a multi select cell that contains 4 email address, separated by CHAR(10). I have been able to isolate the 1st, isolate the 2nd and isolate the 3rd in columns to the right. 1st email is extracted with this formula —> =IF(FIND(CHAR(10), [Names of Approvers needed]@row ) > 0, LEFT([Names of Approvers needed]@row ,…

Formulas and Functions

Discussion List

Help Article Resources