-
Check a box in Parent cell based on children cells
Hello, I need to check a box in a parent cell only when all children cells in that column have been checked. I will manually paste this formula into multiple parent cells in my sheet. The formula I tried did not work...Any guidance or tips on where I went wrong? My thought was if I gave the range of children cells, and…
-
Count if for multiple columns
Hi all, I'm trying to create a count if formula to find data in two columns. For instance I have two columns. One column is status and the other is for discipline. I am trying to count if the status is "complete" and the discipline is "marketing". I need the formula to tell me what is the total count in the Smartsheet.…
-
Formula help - Countifs, contains, and adding an OR function
Hi Smartsheet Experts: I am working on a formula and have gotten stuck. I have a countifs formula, and I need to change it to count customers with either Product A or Product B (some have both) - I can't figure out how to insert an "OR" function into the formula without breaking it. Right now the formula is actually…
-
Weird outcome with Large function
I am experiencing a weird scenario while trying to use the Large function. What I want to do is to get the two most recent dates from a column with numerous dates. Please check the sample datasheet: The Dates column contains the dates and I am showing the two most recent dates in the column Recent Dates. I am using the…
-
@column alternative / is there an @column function?
Hi everyone, I've created a formula that I'd like to replicate over many columns. I know I can paste it and change the column number, but is there a way to instruct the formula use a numbered row (in this case, 20) and the current column? Something like @column =COUNTIFS({April/May 2021 Boot Raw Data Range 1}, [Primary…
-
Percentage of a number within a cell
In Excel, I can utilize the following formula to find the percentage of a number. Input =H5*30.1% Output $11,371.11 When H5=$37,777.78 However, in Smartsheet, the formula does not copy over and when the formula is manually entered in Smartsheet, it tells me that it is unparseable. Manually doing the calculation defeats the…
-
Countifs Formula where one value may be a fragment or a word (but I only want it counted as word)
Hello, I've written a countifs formula for the number of times certain words show up in a column on another sheet based on a date column in that sheet. The formula works fine for most words such as "Recruitment" or "Compliance". However, I have one value that is "NCE" and that's where I'm having the problem. I need to…
-
IF AND formula help
Hello, How do I use an "IF(AND(" formula to get a count of two conditional columns? I am working on calculating a weekly average and I have a date received column which I broke down by "week number:" and "year" in separate columns. However, I have two separate Smartsheets, one being the current task and the other being the…
-
If / then statements with subtraction problem
Hello everyone, I was hoping some of the pro's on here could help me figure something out. I'm creating a hours tracker for employees to use remotely and I need to calculate the overtime hours as anything over 8 or anything over 10 depending on the answer given in a column. So if [certified] = "Yes" then subtract 8 from…
-
Formula to automate a dashboard and reference previous / next cell
Hi, I am trying to build a formula to remove a manual step in a report. I'd need some guidance on how to do. In the screenshot below, I want to automate the % of change calculation. % of change = last week SLO - this week SLO. I think I can use the "Used in Dashboard?" column to build a formula but not really sure how to…