-
How do i write a formula to calculate YTD totals of a given category
Here is the formula i started with. It gives me a #unparceable response =SUMIFS(Amount:Amount, [Expense Category]:[Expense Category], Description@row), Date:Date, YEAR(@cell) = YEAR(TODAY())) Ive included a screen shot
-
Workspace - adding a new sheet to a folder - report is not picking up that new sheet
I have a question about workspace and folders and reports. I have a company workspace with 3 folders in it; Active jobs, cancelled jobs and completed jobs. I have a report that pulls from the active jobs folders, but when I create a new sheet that report does not automatically pull in that new sheet. My question is, what…
-
IFError
Good Day Smartsheet Community: Can I add IFError to this Formula? I tried several times but I keep getting an incorrect or Unparseable =ROUND(AVGW(CHILDREN([% Complete (Planned)]3), CHILDREN(Duration3)), 2) Thanks
-
Can I have blank fields treated as zero in functions
Hi, I am trying to average a range of cells. Two questions: Most important - in some cases the cell is empty. That should be treated as a zero but the AVG function is ignoring the blanks and only taking the average of the populated cells. If there happens to be text in the field (there should not be), then the field can be…
-
Calculating Days Between Estimated and Actual Start Dates (including projects which haven't started)
Hi! I've got a project tracking sheet that has the following columns: Estimated Start Date Actual Start Date I need to calculate how long a project has been waiting to start. Some projects don't have an actual start date, though. So the formula needs to know that blank dates in the actual start column should be counted as…
-
SUMIF statement based on dates
I would like to create a SUMIF equation to only sum data (column F) based on a date range (column B) . It works in excel with the following equation for reference. =SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)-SUMIF($B$9:$B$2000, ">=9/30/2018", $F$9:$F$2000) However, when I import it to smartsheets, I get an error. My…
-
Count of dropdown options (like you can with a pivot table?)
I've created a quick Form to help my HR team collect votes from staff on 4 key areas. I've used the "Dropdown (multi select)" column type, and the Form asks staff to select the 2 options they rank as most important from these lists. Is there an easy way to then see how many votes have been allocated to each option? Through…
-
COUNTIFS
I am trying to count number of lines from project master list that are in a certain region and when the Forecast $ is greater than the Actual $. Looking for projects that were under the Forecast $. Simple formula but will not work. Please help. =COUNTIFS({Region}, =Label$3, {Forecast $}, >"0", {Forecast $}, >{Actual $})
-
RANKAVG Function - exclude cell from range
Hello, I am trying to use the RANKAVG function but cannot figure out how to exclude a cell from the range selection. My equation is: =IFERROR(RANKAVG([Total Score]@row, [Total Score]$12:[Total Score]$77, 0), ""). I do not want to include cells [Total Score]24:[Total Score]26, [Total Score]39:[Total Score]41, [Total…
-
RYG Ball Automation
Hello, I am trying to automate RYG ball based on Y/N responses to other cells OR I could change the Y/N to also be RYG if that would make this possible. Essentially, my overall goal is to automate the RYG ball based on how 26 other fields are answered -- 13 or less are green = Red 14-19 Green = Yellow 20-26 Green = Green…
