-
Which formula do I need "Has" or "Countifs"
I'm having trouble knowing what type of formula to use in my dashboard, and how to get the results that I need. To preface - I am very new to Smartsheet, and the use of formulas so pretty much "learning as I go". What I have here is a result of combined YouTube videos and LinkedIn learning Smartsheet 101 videos. Let me…
-
Color Symbol formula not working
Hi! I'm trying create a formula that shows the following: Blue - Status is complete Green - Estimated completion day it today or in the future Yellow - Estimated completion date is between 1-7 days past the due date it is yellow Red - Estimated completion date is 8+ days past due it is Red. Gray - if there are no dates.…
-
Sum total days in date range which fall on certain days of the week
Where the first instance of 'Days Not Worked' for a member of staff occurs (the first date of that person's annual leave), I want to input a distinct value (only once) in the column 'Deduct N/A Annual Leave'. This is the sum of, where manager type is Company annual leave, the number of days in a date ranges between…
-
IF(AND(OR Statement
I need assistance with a formula. =IF(AND(OR([Specialty]@row = "type1", [Monthly Average Billings]@row >= 50000), [Specialty]@row = "type2", [Monthly Average Billings]@row >= 30000),"Yes","No"))) Type1 meets 50000 = Yes / Type1 doesn't meet 50000 = No or Type2 meets 30000 = Yes / Type2 doesn't meet 30000 = No
-
MROUND to round down?
I am attempting to build a rounding formula to match FDA nutritional labeling guidelines. Using "calories" as an example, below are the rules I have to follow to build out the formula so that someone can type in the calculated value for calories in one column and get guidance on what value needs to be placed on the printed…
-
Need help with Health Status Formula
I have a sheet with 3 columns: Action, Status, Health I want the health to populate a green, red, yellow, or gray circle depending on which status the action is in. The status and corresponding health colors are: Blank = Red Not Started = Red In progress = Yellow Completed = Green Cancelled = Gray On Hold = Gray Looking…
-
Validating a column with formula COUNT
Hi team - I have an ID column that needs to have 18 characters (collected via a form), typically someone filling out the form will include a 16-character ID that breaks our automation and their input is not captured (lots of automation rows moving based on criteria, etc.) I'm thinking that there's a way to use COUNT…
-
How to recreate a payment month Excel formula in Smartsheet
Hoping you might be able to help me with migrating a formula from Excel into Smartsheet. I'm in the process of recreating a payment tracker solution for some colleagues of mine, but I'm struggling to replicate one of the formulas. The formula is as follows: =IF(DAY(C4)<=3,TEXT(C4,"yy-mm(mmm)"),TEXT(C4+30,"yy-mm(mmm)")) The…
-
Pivot table with countifs using multiple criteria
Hello All, I am trying to build a pivot table using the countifs formula that looks at a spreadsheet containing two various criteria - one is by line of business and the other is if a selection = Yes from a drop down menu - they are in two separate columns but within the same spreadsheet and the pivot table is its own…
-
Hello, i need some help how do i connect the %complete column to the status column
for example if the status says in progress i want the %complete column to automatically say 50% can anyone help me?