Formulas and Functions

Archives for September 2023

Weekly/Monthly In and Out Report
I have created a system to track inventory for a large warehouse for my company. We receive and ship out a lot of equipment daily. I am looking to create a weekly and monthly report to give to management to show the amount of Square Footage that has been received and the amount that is shipped out. Each company we store…
Finding Duplicate Formula not working
I'm trying to find duplicates in [Invoice Number] column. I added a column "Duplicates" and wrote the formula: =COUNTIF([Invoice Number]:[Invoice Number], [Invoice Number]@row) Then I could conditionally format any count over 1 and thus finding the duplicates. Its working for the most part, but I have identified one…
MULTIPLE IF FORMULA AROUND DATES
Hi: Can anyone help with a Multiple If formula which i cannot get to work please? I have a Valid To Culumn with a date in it and have a Status Column i want toi flag as CURRENT, EXPIRING or EXPIRED depending on the date. I had CURRENT & EXPIRED working but when i try to include the additional IF to show as EXPIRING if the…
Cumulative Column Query for Sheet A and Sheet B
Hello, I have Sheet A, which contains a column with work dates that have been inspected. I would like to create a cumulative column in Sheet B based on the maximum date from Sheet A. However, it appears that the current setup is cumulating all the dates in Sheet B instead of considering the maximum date from Sheet A. I…
Quality controlling data entry to prevent duplicates
I have a vendor database with over 1,000 unique vendors and am looking for a way to quality control new entries to avoid duplicates in a VENDOR NAME column (note that there is a separate VENDOR ID # column). One way of doing this seems to be through formatting the VENDOR NAME column as a dropdown while NOT restricting to…
Attempting to add similar values with Sumif
I have a sheet that could list the same values multiple times as they are being populated via a Zap from Google Sheets. So that I can find a true total value of the Job Name, I am counting the times that they appear on the sheet and then dividing the Total Amounts of Sale for a Job by the number of instances on the sheet,…
Pulling data based on most current entry and multiple criteria with other entries on same day?
I am trying to extract data from a sheet and summarize the most current data point based on: 1) Matching a reference cell (Sample Location). 2) Ignoring blank entries since some rows may have data in some columns but not others. 3) Accounting for different reference cells that have data entered on the same date. The below…
Multiple columns using same datatable to lookup values
Hello everyone, I have a sheet that stores part numbers / stock codes. There are 5 columns that have a part number. The "master" part number list has 45K records, and I have successfully stored this as a datatable. I have also linked this datatable to one column in my sheet, using the stock code, to look up the…
Count Collect Contains formula
I'm trying to create a metric sheet to compile results from a number of survey questions. For questions where there is one answer per question, this formula works fine: =COUNT(COLLECT({Survey Intake Name}, {Days Remote}, [Answer1]3)) {Survey Intake Name}is the base value to count, {Days Remote} is the reference to the…
How to count cells when they have multiple values selected, and I'm only wanting a specific one?
Because we can't use wildcards on the program, I am at a loss to set up a report that summarizes my sheet. I'm trying to determine how many entries are the 'Status' outlined in each Primary Column row, where the 'Suggest Solution' is the text in each column header. The 'Status' can only be one value, but the 'Suggested…

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

Automatic Phase from WorkBreakdown
Hello all! I'm modifying the Simple Portfolio Work Breakdown Schedule and having a little trouble. I like the way the Work Breakdown structure works but I'd really like an automatic way to track which Phase something is in. I know I could just add a column and manually select which Phase tasks and sub tasks are in, but I'm…
How to calculate NPS Score from Smartsheet Sheet
Hello, I have tried to calculate our NPS score, but am getting either UNPARSEABLE or 0. What am I doing wrong here? I've tried this formula (NPS Score 1): And this one (NPS Score 2):
Identifying oldest entry on sheet, for different groups
Hello! I am working on a "waitlist" process, where the sheet will have multiple candidates under each work location. I need a formula to identify the oldest entry for each location, and if there are multiple entries for the earliest date, use a "score" column identify the higher score as the next candidate in line. Example…

Formulas and Functions

Discussion List

Help Article Resources