Formulas and Functions

Can Formulas Reference Reports?

UnaB 274 views 11 comments 0 points Most recent by Paul Newcome 03/23/20

First post 11/20/18,Last post 03/23/20
I'm trying to see if formulas can reference reports or just root SS. I ran a report for all late tasks assigned to all people in a department. I then wanted to use a COUNTIF formula that would sum up all the late tasks of a certain person.…
Formulas and Functions27411
UnaB
Sumif from multiple sheetsAnswered Question

Answered JennS_ 23 views 1 comment 0 points Most recent by Sean Morgan 03/23/20

First post 03/19/20,Last post 03/23/20
I need help with a formula for 2 scenarios: 1) I want to sum up the total of items from the "ATL Tasks/Notes" sheet that have any priority designation (high, medium or low). 2) Then I also want to sum up the total of items betw…
Formulas and Functions231
JennS_
Need to calculate advance per week with a range of two dates.Answered Question

Answered Maria Chicas 20 views 1 comment 0 points Most recent by Genevieve P 03/23/20

First post 03/20/20,Last post 03/23/20
Dear all, I need to support to determine a formula that allows me To perform a weekly count of how many days (or if If any) will happen this week based on range of between 2 dates. Example Today is 3/19/2020 Start Date End Date 3/16…
Formulas and Functions201
Maria Chicas
Cell 1 to equal Cell 2, if 2 blank, then 3, if 3 blank, then 4Answered Question

Answered Kylie English 17 views 1 comment 0 points Most recent by Paul Newcome 03/23/20

First post 03/22/20,Last post 03/23/20
Hi all, I have 4 cells, what I need is: Cell 1 to equal Cell 2 If Cell 2 is blank, then read Cell 3, If Cell 3 is blank, then read Cell 4. I have searched quite a few discussions to try and replicate but cannot seem to get it to work. Ce…
Formulas and Functions171
Kylie English
Pull Text from String using DelimiterAnswered Question

Answered Ramsay Zaki 15 views 1 comment 0 points Most recent by Paul Newcome 03/23/20

First post 03/23/20,Last post 03/23/20
I would like to parse the text from a string in another cell. However, the string might not always be the same length so I cannot use LEFT or RIGHT. Here are two examples of what the string might look like: 1,5,7,33,15 13,22,8,1,16 Both ex…
Formulas and Functions151
Ramsay Zaki
Help With Adding More Conditions in Current IF FormulaAnswered Question

Answered An Nguyen 23 views 11 comments 0 points Most recent by Paul Newcome 03/23/20

First post 03/18/20,Last post 03/23/20
Hi everyone, I am currently struggling to add more conditions to my current formula. The formula is: =IF('Department 1'[Approval Response]="Pending" && 'Department 1'[Past Due/Scheduled]="Past Due",1,0) I want …
Formulas and Functions2311
An Nguyen
Date formula returning "#INVALID DATA TYPE"Accepted Answer

Accepted Answer Shaun Dickens 19 views 3 comments 0 points Most recent by Paul Newcome 03/23/20

First post 03/20/20,Last post 03/23/20
Hi guys, I can't figure out what's happening here, hoping someone can point me in the right direction Importing a table with a "Date" column set as primary column, Date format is YYYY-MM-DD Trying to convert the value into a form…
Formulas and Functions193
Shaun Dickens
Adding a number of months to a date using formulasAccepted Answer

Accepted Answer colin alexander 31 views 4 comments 0 points Most recent by Paul Newcome 03/23/20

First post 03/19/20,Last post 03/23/20
I have a calibration sheet that lists the number of months required between calibration dates and a column that states when the last calibration was carried out. I would like to use these 2 columns to calculate the date when the equipment …
Formulas and Functions314
colin alexander
Incorrect counting of blank rowsAccepted Answer

Accepted Answer Angela Ryer 28 views 5 comments 0 points Most recent by Paul Newcome 03/23/20

First post 03/19/20,Last post 03/23/20
="Completed " + COUNTIF([Completed Tasks]:[Completed Tasks], 1) + " of " + COUNT([Completed Tasks]:[Completed Tasks], IF([Task Name]:[Task Name], "")) I have a checkbox column that will read in the summary ba…
Formulas and Functions285
Angela Ryer
Verify All Sites Submitted Response TodayAnswered Question

Answered Amy Arnold 14 views 1 comment 0 points Most recent by Genevieve P 03/23/20

First post 03/19/20,Last post 03/23/20
Hi, I'm trying to track if a site (or Name) created a new daily entry using a Smartsheet form. I added this formula to the Updated Today column to return a "Yes" answer if the site (or Name) created a new daily entry. This seems…
Formulas and Functions141
Amy Arnold
Formula to place a date in a cell that is greater than a date in another cell by "x" lead timeAccepted Answer

Accepted Answer [email protected] 20 views 5 comments 0 points Most recent by Andrée Starå 03/23/20

First post 02/26/20,Last post 03/23/20
Hello again community, Just wondering if somebody knows how to write a formula that could look at a date in a date ordered column and place a date in another column that is greater by 30 days. From this point, I have an idea to have a for…
Formulas and Functions205
[email protected]
Statement to read a text in one cell, then add a numeral (based on the text) to the date in anotherAnswered Question

Answered Kylie English 16 views 2 comments 0 points Most recent by Genevieve P 03/23/20

First post 03/22/20,Last post 03/23/20
Hi all, help please :) I have 3 columns - Approval Due Date Requested Date Priority Step 1 - Approval Due Date to look at the Priority Priority will be High, Moderate or Low / High = 7 days /Moderate = 14 days / Low = 21 days THEN Lo…
Formulas and Functions162
Kylie English
Multi-Select Column Formula to Add ValuesAccepted Answer

Accepted Answer mel.greenspan 14 views 3 comments 0 points Most recent by Andrée Starå 03/22/20

First post 03/20/20,Last post 03/22/20
Is there a way to add values to a multi-select column using a formula? I have several sheets that i want to switch from checkbox columns to a single multi-select column "Subscribed Calendars". It is VERY manual right now doing it…
Formulas and Functions143
mel.greenspan
I want to count dates in the colum "Solldatum" that are smaller than today.Accepted Answer

Accepted Answer Michael Pichler 8 views 3 comments 0 points Most recent by Andrée Starå 03/22/20

First post 03/22/20,Last post 03/22/20
https://us.v-cdn.net/6031209/uploads/034/B5HVBCHN3QWJ.png I tried it with countif and it worked in Excel in German with the following formula: =ZÄHLENWENN(A2:A13;"<"&HEUTE()) as Example. What do I have to do in smarthsheet…
Formulas and Functions83
Michael Pichler
Formula to Count Check Boxes in a Check Box ColumnAccepted Answer

Accepted Answer dskpetersen 47 views 5 comments 0 points Most recent by Andrée Starå 03/21/20

First post 01/30/20,Last post 03/21/20
Trying to count in coumns how many boaxes are checked per column - Keep getting Boolean Expected. There are the formulas I have tried: =COUNTIFS([Mike M]5:[Mike M]135, 1) =COUNTIF([Mike M]5:[Mike M]135, 1) Is it possible to created this …
Formulas and Functions475
dskpetersen
Counting physical inventory while removing duplication...Answered Question

Answered Drew Frazelle 35 views 4 comments 1 point Most recent by Andrée Starå 03/21/20

First post 02/14/20,Last post 03/21/20
I'm trying to count the number of unique pieces of equipment that are on my worksite. Each unit has a specific Unit Number listed in a sheet. Units that are On-Site are indicated by a green dot in another column. The tricky part for me is,…
Formulas and Functions354
Drew Frazelle
Dynamic Cell linking empty fields

Greg Hjertager 17 views 8 comments 0 points Most recent by Andrée Starå 03/21/20

First post 03/20/20,Last post 03/21/20
I'm trying to use cell linking but it won't let me link a cell that doesn't have data in it yet. My plan is to have it link to a cell that will have data put in it gathered from a form. The end result is that I'll have two sheets. The fi…
Formulas and Functions178
Greg Hjertager
Which formula should I use if I want to add up all values from a column?Answered Question

Answered Jennifer Molina 13 views 1 comment 0 points Most recent by Andrée Starå 03/21/20

First post 03/21/20,Last post 03/21/20
To give a little bit of context, I am creating a report where I can count whether a cell has been filled with information. The column I am using is a drop down list with 5 values. Let's say these values are apples, bananas, oranges, pears,…
Formulas and Functions131
Jennifer Molina
#INCORRECT ARGUMENT SETAccepted Answer

Accepted Answer Amanda Chu 656 views 17 comments 0 points Most recent by Andrée Starå 03/21/20

First post 03/13/19,Last post 03/21/20
Hello, I cannot figure out why this SUMIFS is not working. When I break up the SUMIF criteria, they both work fine. But when I put them together, I get #INCORRECT ARGUMENT SET error message. =SUMIFS({MH Client Profiles v2 Range 13}, &quo…
Formulas and Functions65617
Amanda Chu
Can Conditional Formatting trigger specific formulas?Answered Question

Answered Mark Silvester 13 views 5 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/20/20,Last post 03/20/20
The spreadsheet in question is a shipping/inventory database. https://us.v-cdn.net/6031209/uploads/497/1R9QZYQQS423.png The top rows where Device SKUs are listed are the total contracted amount of devices a client pays for. Then as we cre…
Formulas and Functions135
Mark Silvester
Does the COUNTIF Function work if the criterion have decimal places?Answered Question

Answered Alexa Rice 17 views 6 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/20/20,Last post 03/20/20
I am working on an extended process and I want to sub-categorize a set of steps. Ideally I want to use the 1.1, 1.2, 1.1.1 as step headers but when I try and do functions to count cells it is not working. Example: https://us.v-cdn.net/…
Formulas and Functions176
Alexa Rice
LOG FunctionAnswered Question

Answered coryg 11 views 1 comment 0 points Most recent by Paul Newcome 03/20/20

First post 03/20/20,Last post 03/20/20
Is it possible to calculate (or even approximate) a LOG function in Smartsheet? We regularly use the below formula to calculate A/E fees: (35/LOG(Construction Cost))-1.15)*(Complexity Factor) = A/E fee % I have a sheet setup where someon…
Formulas and Functions111
coryg
How can I compare two cells in a cross sheet reference?Accepted Answer

Accepted Answer Janet Skjerven 16 views 3 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/20/20,Last post 03/20/20
Hello, I have a COUNTIFS formula (below) that returns the number of items that pass all the tests. Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beau…
Formulas and Functions163
Janet Skjerven
Averageif without maximum paymentAccepted Answer

Accepted Answer Jörg Schmidt Novum Hospitality 16 views 1 comment 0 points Most recent by Paul Newcome 03/20/20

First post 03/19/20,Last post 03/20/20
Hallo, I want to calculate the average damage without considering the maximum damage. I tried this: =AVERAGEIF(Zahlung4:Zahlung140; >1) - MAX(Zahlung4:Zahlung140) but it doesn´t work. I get a - value Any idea ? 🙄
Formulas and Functions161
Jörg Schmidt Novum Hospitality
Is it possible to trigger an alert notification ONLY if the value of a field increases?Accepted Answer

Accepted Answer alcapps 9 views 3 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/20/20,Last post 03/20/20
I have a formula that counts absences for the past 6 months. A formula in another column looks at the first and when that number reaches 3, it adds an occurrence (value changes from 0 to 1). I've set up automation to notify the supervisor …
Formulas and Functions93
alcapps
Different types of zeros?Accepted Answer

Accepted Answer Preston 13 views 5 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/19/20,Last post 03/20/20
The formula below is creating multiple "zeros". See pictures. What am I missing? https://us.v-cdn.net/6031209/uploads/587/DYEAMAQ4PA89.png https://us.v-cdn.net/6031209/uploads/413/EOJGS05QYULC.png =IF([email protected] = "ERROR&…
Formulas and Functions135
Preston
IF(OR with RYGGAccepted Answer

Accepted Answer Tiera Mack 25 views 7 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/18/20,Last post 03/20/20
I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff. Yellow=In progress Red=Overdue Grey = Not started Green=Completed So IF start date …
Formulas and Functions257
Tiera Mack
Calculation in column is not auto-populatingAnswered Question

Answered Madison McGuire 21 views 3 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/19/20,Last post 03/20/20
I have a spreadsheet with a calculated column (If statement based on previous column). I regularly paste in new rows, but the calculated columns do not always auto-populate. When I hover over the calculated cell, the calculations are prese…
Formulas and Functions213
Madison McGuire
SUMIFSAccepted Answer

Accepted Answer MJLaughlin 21 views 3 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/19/20,Last post 03/20/20
I am trying to create a SUMIFS formula that will allow me to pull data from the following columns: Region: APAC (There are several offices that make up the APAC region) OfficeStatus: Closed OfficeHeadCount: Sum Total (For example 8 APA…
Formulas and Functions213
MJLaughlin
Getting #INVALID DATA TYPE using IF(AND and IF(OR togetherAccepted Answer

Accepted Answer BTeeter 19 views 3 comments 0 points Most recent by Paul Newcome 03/20/20

First post 03/19/20,Last post 03/20/20
=IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1, "Green"), IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red")) Formula above. …
Formulas and Functions193
BTeeter

Formulas and Functions

Formulas and Functions

Discussion List