# Formulas and Functions

## Discussion List

• Paul Charlebois 35 views 2 comments Most recent by Paul Charlebois
First post 02/12/18,Last post 02/15/18
Hello,   I have a particular problem, I have a series of cells that have formulas to detect the checked values of a series of Check Boxes, creating conditional formatting and inserting 'N/A' in certain cells: =IF([Assemblage Mecanique]8…
Formulas and Functions352
Paul Charlebois
• americansurveymanagement35236 251 views 5 comments Most recent by ricki
First post 02/15/18,Last post 02/15/18
Would anyone have a example of a formula to count checked boxes in a column divided by the number of unchecked boxes. Any help will be greatly appreciated. Thanks.
Formulas and Functions2515
americansurveymanagement35236
• Kal-El 13 views 2 comments Most recent by Kal-El
First post 02/14/18,Last post 02/15/18
Good afternoon Smartsheet Community:   I am trying to add the total from the count of 2 sheets given the criteria. However, I am receiving an INCORRECT ARGUMENT SET when using SUM or Count conditions (formula below). Any help appreciate…
Formulas and Functions132
Kal-El
• DV 47 views 1 comment Most recent by Jim Hook
First post 02/14/18,Last post 02/14/18
I have this formula copied down a column: =vlookup(A1,ReferenceAnotherSheet,3). The first row bring back the correct number value. The other rows are bringing back a #nomatch. Also, I want the cell to return the total of all cells in co…
Formulas and Functions471
DV
• Mary Ayers 111 views 5 comments Most recent by Mary Ayers
First post 02/14/18,Last post 02/14/18
I'm trying to write an IF/AND statement that references a range of data in another Smartsheet but an #INVALID OPERATION message is returned. * IF the user is requesting to use the Immersive Learning Center * AND the user is requesting to …
Formulas and Functions1115
Mary Ayers
• brandon.elmer 24 views 3 comments Most recent by Mike Wilday
First post 02/13/18,Last post 02/14/18
I use the formula when counting late tasks:  =IF(ISBLANK(Finish107), "", IF(AND(TODAY() > Finish107, [% Complete]107 < 1), 1, "")) The issue with this is that it also counts summary tasks which really aren't task…
Formulas and Functions243
brandon.elmer
• brandon.elmer 68 views 1 comment Most recent by claytonrlewis
First post 02/14/18,Last post 02/14/18
To count the number of unchecked boxes I'm using the formula:  =COUNTIF([% Complete]3:[% Complete]60, 0).  The issue is that it is also counting blank rows.  How do I get it to only count actual blank checkboxes?  (Image)
Formulas and Functions681
brandon.elmer
• EvaM 268 views 1 comment Most recent by Shaine Greenwood
First post 02/12/18,Last post 02/13/18
I am attempting to create a project status column that is using the Harvey Balls (red, yellow, green and gray). I am wanting these to automatically change yellow when my due date column is 120 days with the due date, I would like it remain…
Formulas and Functions2681
EvaM
• Diane Martell 15 views 1 comment Most recent by Shaine Greenwood
First post 02/11/18,Last post 02/13/18
Hello ~ I'm calculating attendance and want to highlight inactive participants. Currently, I copy and paste  =COUNTIF([20 1/6/18]3:[25 02/10/18]3, "P") each week as a new column is added. Is there a formula that will auto add ne…
Formulas and Functions151
Diane Martell
• hquinn 14 views 3 comments Most recent by Shaine Greenwood
First post 02/06/18,Last post 02/13/18
Hi,  (Sorry if this has already been asked and resolved, I couldn't find anything).  I currently use smartsheet for our team's training log. In doing so, there are several columns that require numerical data that I would like to keep tra…
Formulas and Functions143
hquinn
• DanielGD 40 views 3 comments Most recent by Mike Wilday
First post 02/09/18,Last post 02/13/18
Getting an Invalid Data Type error with this formula: =COUNTIF(AND(FacilityCount:FacilityCount, "TZ"),NOT(COUNTIF(Status:Status, "Green"))) The second "CountIf" is a symbols column.  Looking to make a count…
Formulas and Functions403
DanielGD
• brandon.elmer 423 views 3 comments Most recent by Mike Wilday
First post 02/09/18,Last post 02/13/18
I'm trying to count the number of late tasks in a project plan.  The formula I'm using works as long as a row isn't blank.   =IF(AND(TODAY() > Finish15, [% Complete]15 < 1), 1, "").  If I drag the formula all the way down t…
Formulas and Functions4233
brandon.elmer
• [email protected] 153 views 2 comments Most recent by [email protected]
First post 02/09/18,Last post 02/13/18
I'm trying to determine a COUNTIF formula to show what deliverables are due in the next 7 day period based upon a Due Date column. However, I'd like said formula to only showcase what deliverables are in the next 7 days and ALSO be able to…
Formulas and Functions1532
[email protected]
• Mikey67156 91 views 1 comment Most recent by Mikey67156
First post 02/12/18,Last post 02/12/18
I'm trying to use VLOOKUP for the first time, and perhaps I just don't understand how it all works.  I keep getting an "invalid data type" error.  Here's my formula.  =VLOOKUP(Workorder10, {Platform MPS Range 3}, {Platform MPS R…
Formulas and Functions911
Mikey67156
• caroline_allen37286 35 views 4 comments Most recent by caroline_allen37286
First post 02/10/18,Last post 02/12/18
I am using VLOOKUP in a sheet (call it Sheet2) to pull data from anotther sheet (Sheet1). Then, I'm creating a report that pulls data from Sheet2. This report is pulling some of the data from these VLOOKUP columns. I'm seeing an issue whe…
Formulas and Functions354
caroline_allen37286
• joechap 334 views 4 comments Most recent by joechap
First post 02/09/18,Last post 02/12/18
I can't figure out how to make the function work or I'm trying to do something not supported. Basically, I want to pull the value of a cell from a row on sheet B and put it in a cell on the same row in sheet A. I'm not looking to perform …
Formulas and Functions3344
joechap
• ABogle 67 views 2 comments Most recent by ABogle
First post 02/07/18,Last post 02/12/18
Hello! I am trying to find a way to determine resource allocation in project plans so that they more accurately reflect the availability of my team in the resource view. We have individual project plans for each project, which all fe…
Formulas and Functions672
ABogle
• jiwhite 146 views 2 comments Most recent by jiwhite
First post 02/09/18,Last post 02/12/18
I'm very confused about how VLOOKUP has been implemented. * Can the search value reference a value in another column in the current row, or can it only be a string enclosed in quotes?  If it can only be a string, it makes the function pre…
Formulas and Functions1462
jiwhite
• James Maxwell 117 views 2 comments Most recent by Daniel Stein
First post 02/11/18,Last post 02/12/18
I have a sheet of VLookups which I get to be working perfectly then when I come back later (sometimes as little as 10-15 minutes) most cells have been changed to "No Match". To fix this I simply have to re-input the range to what…
Formulas and Functions1172
James Maxwell
• c-justin.grey37336 45 views 1 comment Most recent by Shaine Greenwood
First post 02/11/18,Last post 02/12/18
I have two columns "Actual Start Date" and "Actual End Date", I want to have a formula for the status column using the RYG Status balls that will: - Red when nothing is in either of the two cells - Yellow when there i…
Formulas and Functions451
c-justin.grey37336
• [email protected] 39 views 1 comment Most recent by Jim Hook
First post 02/12/18,Last post 02/12/18
I'm trying to determine a COUNTIF formula to show what deliverables are due in the next 7 day period based upon a Due Date column. However, I'd like said formula to only showcase what deliverables are in the next 7 days and ALSO be able to…
Formulas and Functions391
[email protected]
• JDuncan 14 views 1 comment Most recent by Andrée Starå
First post 02/09/18,Last post 02/11/18
Hi there, I have created a document designed to check off whether or not we have received scores back from our partners using a column of check boxes. I need to input a formula that counts the boxes that have been checked off versus the to…
Formulas and Functions141
JDuncan
• Stefan 7k 14 views 3 comments Most recent by Stefan 7k
First post 02/08/18,Last post 02/10/18
Hi community, I had a hard time with Smartsheet support (used to be different) regarding my issue. Has anyone else with Smartsheet set to german speaking setup the problem described below? Starting some time last week, Smartsheet starte…
Formulas and Functions143
Stefan 7k
• Adam Kinney 142 views 2 comments Most recent by Shaine Greenwood
First post 02/08/18,Last post 02/09/18
Hello, This may have been asked previously however I cannot seem to find my specific request that only reference if a date is entered (not range related) so here goes.  I have a Status column on a sheet and have it set to symbol: Harvey B…
Formulas and Functions1422
• c-justin.grey37336 189 views 2 comments Most recent by Mike Wilday
First post 02/09/18,Last post 02/09/18
I would like to have the status "ball" color change to red if the column cell is blank and green if the column cell has a date entered in it.   PLEASE HELP!!!!!!
Formulas and Functions1892
c-justin.grey37336
• Paul Bartley 89 views 3 comments Most recent by J. Craig Williams
First post 02/07/18,Last post 02/08/18
Hi, Newbie question here. Let's say we have a simple SUM formula: =SUM([Column1]15:[Column4]15) Is there a way to specify the row # using a variable or some kind of reference to a number value in a cell? For example, rather than "…
Formulas and Functions893
Paul Bartley
• claytonrlewis 14 views 1 comment Most recent by J. Craig Williams
First post 02/08/18,Last post 02/08/18
So with the new referencing sheets feature (at least I think it's new) is it possible to have one sheet that would be the master for all summaries and metric information rather than roll them up into extra rows at the top of each sheet?  …
Formulas and Functions141
claytonrlewis
• HebaH 49 views 1 comment Most recent by J. Craig Williams
First post 02/08/18,Last post 02/08/18
Hi All,  Is there a way to send a daily notification when a task is not complete? The way my sheet is set up is the RYG dots are automated depending on the due date, and based on those dots, notifications get sent (Yellow is the due date,…
Formulas and Functions491
HebaH
• M.Cetrix 35 views 1 comment Most recent by Shaine Greenwood
First post 02/07/18,Last post 02/08/18
Hi, I am able to setup a widget that is link to a direction symbol (up, down, neutral) for my data. What i want to do is have a customer image for each (up,down,neutral). Is it possible to have a conditional statement that will presen…
Formulas and Functions351
M.Cetrix
• Jaraldo 50 views 1 comment Most recent by Shaine Greenwood
First post 02/05/18,Last post 02/08/18
Hello, I'm trying to build a template which would allow vendors to log hours against a project and automatically calculate a cost for each task. This calculation looks at the name of the individual reporting hours along with the listing of…
Formulas and Functions501
Jaraldo