-
Help with an IF statement: adding a word based on a blank cell
I have the formula below in the primary field so it populates more information to the calendar view. The problem I am having now is that if Assigned5 is empty it adds a gray mark to the calendar view. I would like to add Hold if the cell is empty. (Note, the cell "Assigned" is a checkbox =IF(Assigned5 = 1, [Agency Name]5 +…
-
Can you use 2 joins in the same formula
IF Type = Project join Type+ID+"-"+PMO Project Name If Type = Request join Type+ID+"-"+Request Name I've tried this a couple of ways - if I select Request - it comes up as Request1000-RequestName If I select Request then it blanks out. instead of Project1000-ProjectName Formula =IF(Type@row = "Rqst", JOIN(Type@row + [Rqst…
-
Nested IF with Multiple criteria
I am working on an approval by facility. The first iteration had one approver list, this is working great. =IF([Contract Expense Variance]1 < 5001, (VLOOKUP("5K", {Voting List Levels Range 6}, 2, false)), IF(AND([Contract Expense Variance]1 > 5000, [Contract Expense Variance]1 < 15001), (VLOOKUP("15K", {Voting List Levels…
-
How to use MONTH(sheetrefencerange) in a SUMIFS function?
I cannot get the function below to calculate correctly. I keep getting 0 for the sum. =SUMIFS({Hours}, {Input Date}, MONTH({Input Date}) = 4, {Project ID}, [Column2]@row) {Input Date} is a column reference range in another Smartsheet. {Input Date} is an auto-generated date/time column.
-
I need to sumif a column if a checkbox is not checked.
Hi - I need to sum the supplier score column if the checkbox on the row is NOT checked. Thanks for your help! DN
-
COUNTIFS for multiple ranges
I am trying to use COUNTIFS to add the values in multiple ranges for items that have a value of PASS. So far I can only pull the value in 1 range and i get "unparseable" if I try to add the values together---any ideas? =("PASS " + "[" + (COUNTIFS(Status12:Status16, "PASS")) + "]")---this works only for 1 range of values
-
Formula Help: Combining Checking a Box with a % Complete to Return a Status Ball
I'm having a hard time combining a formula that when I check off the "Done" checkbox it turns the % Complete to %100 and also changes the health to green as well as combining the criteria of this formula: =IF([% Complete]12 < 0.5, "Red", IF([% Complete]12 < 1, "Yellow", "Green")) Is this possible?
-
Best formula to return a list of qualifications for card view
Hi Community, So, I am trying to save myself a lot of manual data entry. Ultimately, I am trying to take a spreadsheet that was created in Excel, to capture each persons "qualifications" in a specific field, and get it into card view. I have attached the files of what I am starting with and what I would like to end up…
-
Date not recognized in column
I have data coming into Smartsheet from a Microsoft Form, and I'm not sure how to get smartsheet to recognize the data as a date. I tried to create an additional Date column, and putting in a formula to copy the date from the original column (hoping that the settings of the new date column would make the data officially a…
-
Excel formulas to Smartsheet formulas.
The following formulas did not transfer when importing an excel sheet into Smartsheet. They appear as #INVALID or #UNPARSEABLE. Are the following Excel formulas able to convert to Smartsheet formulas? If so what are those Smartsheet formulas? =IF(ISERR(ROUND(SUM(K12/G12),2)),0,ROUND(SUM(K12/G12),2)) =SUM(G12:G13)…