-
Need help with COUNTIFS in sheet summary utilizing 2 columns from the same sheet
I have a sheet with Start Date (date properties), which I have converted to Current Year (text/number properties) in a separate column and then another column called Fully Vaccinated? with dropdown single select properties. In the sheet summary, I have the following formula: =COUNTIFS([Fully Vaccinated?]:[Fully…
-
Counting Unique Values Under Parent Rows
My sheet current has four project types (parent row.) Underneath each project type is the project name (children.) And underneath each project is a list of the team members working on that project (ancestors.) I need to be able to count each unique employee working on projects under each Project Type. So if an employee is…
-
Index/Match formula inconsistent
I have 2 simple index/match formulas to bring over information from one sheet to another. However sometimes the formula returns a #NOMATCH, seemingly dependent on how the data in the data in the other sheet is sorted? There are not parent/child rows so I don't understand why having the rows in a different order would…
-
Partial result
Hello, I'm attempting to create a way to review an account number from one cell, and have a different cell populate just the last four digits with X's for any other remaining characters. So account 1234567 would show XXX4567, where as account 123456789 would show XXXXX6789. Fairly new to complex formulas and not sure where…
-
Formula to include weekends & Holidays in count but exclude weekends as a date for the result
I'm converting a file from Excel to Smartsheets for Quarterly, Semi-Annual, & Annual inspections. I want to set the first inspection date up and then have formulas in place to auto assign the remaining inspection dates for the next 2-3 years. I'm using WORKDAYS with an if formula which is providing a result but it's not…
-
Find "soonest" or closest date in a column of dates.
I'd like to find the earliest or Next available date for 3D Printing Dashboard. I'd like to use IF or IFS given that I would like to search only rows with a "Printing" Status. There are no formulas that seem to help. Any help would be appreciated
-
Trigger a daily automation if a record doesn't exist
Hi there, Our warehouse leads use a smartsheet form to upload a document once a day, around noon. I want to have a notification sent to the manager of a warehouse if that warehouse didn't upload a file for that day. The sheet that the form is built on looks like this: DC is the warehouse number. The formula works for…
-
Nested IF / THEN / SUM Formulas - Based on Values in Drop Down List
Hello! I am working on a Commission log and tracking project. The commission amount is based on the amount billed which is contingent on the contract billing cycle rather than the total cost of the sale. So, I am trying to create a formula that will calculate the billing amount based on the bill cycles from a drop down…
-
Receiving error when using If/And
I have a sheet with the following columns, Date Opened, New - Review Date, Working Date, Alert column, and Status. New-Review and Working Dates are automated by the system when the Status changes to one of those options it records the date. I am trying to write a formula to fit the below scenarios: If one day has passed…
-
How to eliminate false counts on created date (auto-number deleted line items)
Hi there, Thanks for helping with this. I'm creating a sheet summary formula that does a count of items created within a specific year. All good there. =COUNTIFS([C-Year]:[C-Year], "2022") The wrinkle is that the sheet has autonumbering and so I'm getting a false count with 4 items that were created and then deleted. No…