Formulas and Functions

Archives for August 2023

COUNTIFS formula looking at date columns in a reference sheet
I used the Inspection Tracking template set a base. I liked it as a starting point, and added expiration dates. Looking at the metrics sheet provided, it uses this formula to look at the number of violations per month. I also want to see the number of date violations that occurred per month. Date violations would be…
I can's send a reminder to an employee.
How do I resolve that I can not create an automation reminder to a specific employee? I've created many reminder automations for all other staff without a problem. They all have the same access and user profile. The response I receive from smartsheets is: Please advise. Thanks, Maria Vita 203-535-7288 mv@vdgarch.com
vLOOKUP SOMETIMES RETURNS NO MATCH
Hi Everyone, Thanks to some very helpful advice on here I've amended our company quotations sheet to reference a credit checking sheet and then show if a client has been credit checked and what their limit is. 99% of the time this works perfectly, from time to time however it returns #NO MATCH this is without exception for…
Formula to count individual option in multi select list within sheet summary
My sheet has a column called "Skills" which has 7 multi select options. I would like to include a sheet summary that counts how many times each option is selected. In this screenshot the formula =COUNTM(Skills:Skills, "Policy writing") counts everything in the Skills column, not just "policy writing". In the formula…
Collect formula for child rows
I am trying to return the sum of "in process" items under each parent row in the following sheet but can't come up with the right formula/syntax to work. I got a formula to correctly return teh number of child rows but now need the number of items "in process" per parent row and the following formula gives me UNPARSEABLE…
Remove duplicate formula has changed and now need to be corrected.
This is the formula to remove a duplicate that is already on a report: The number circled in read was not part of the original formula and I don't know why it is there. I have found a duplicate on the report where before all duplicated were being removed. Does anyone know why this has happened and how to correct this?
average formula is not working to calculate total retention and total turnover
First row is retention rate and second row is turnover and it keeps giving me an error. Below is the formula i am using for average this is the formula i used to get turnover percentage: But every time I put the average I get an error. Can somebody please help.
Formula Needed. - IF Blank, then Index another cell
Hi, I am looking for some help with a formula. I need a formula that will "pull/copy" the date entered in the column titled REV. START DATE. However, if the REV. START DATE is blank, then I need it to pull the date from the ORIGINAL START DATE column located on the same row. I was originally using the =MAX([Original Start…
Match formula
Is there a way to have a sheet update from another. for isntance if the addresses match i need the new information submitted in one of the columns to then be entered into the column of the other sheet. Also, aditionally if someone uses the same address each month as a location but i need the most recent submission with…
Split Contacts Past 20 Into a Separate Column
Hi, I have a form that is filled out to send a notification to a group of staff. The person creating the message fills out the form and pastes the list of email addresses (separated by semicolons) from another source to populate a Contact List column and automation sends a notification and reminders to the staff on that…

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

If cell is blank
=IF([Audit Due]@row < TODAY(), 1, 0) if the Audit due is blank I want the the answer to be 0. How would I write this formula?
Total invoiced within a date range
Hello, in my example attached we are looking to incrementally add the "PO total amount" invoiced as we will add more rows. We need to add for the first quarter so dates going from 1/1/25 to 3/31/25….we are trying to use that formula but not working: =SUMIFS([PO total amount]:[ PO total amount], [PO’s date]:[ PO’s date],…
If Statement with Multiple Factors
Hello, Sorry for needing additional formula help. For the HTN Kit Helper, I need to it either display Blood Pressure Kit with the Educational Language Helper, except if BP cuff helper is Extra Large Cuff I need it to only display "Extra Large Cuff" in the HTN Kit Helper column =IF([CK Type Helper]@row = "Blood Pressure…

Formulas and Functions

Discussion List

Help Article Resources