-
Building a sheet to best populate dashboards
Do you have any training material you can share on “best practices” for building a project plan/timeline to populate Dashboards correctly (e.g. metrics and tables). Examples of things I want to do: Present the overall project health as red, yellow, green; sum of late tasks; sum of tasks in progress and on-track; sum of…
-
SUMIFS between date range formula not working
Hi All, I am trying to sum the total overtime hours cost for various roles on a project and I am breaking this down into weeks, so I need my formula to look at a range between 2 dates. My formula works correctly for some roles but not others. The OT hours information is collected via a smartsheet form which, amongst other…
-
Auto-number Column and MAX COLLECT with Criteria
Hey friends! I want to combine the two below operations into one column: I'd like to return the highest value in Auto-numbered column when the criteria is met from another column. ~Return the highest/largest auto-number, from the "Row ID | Auto-Numbered" column, matched with each ID# in the "⚠Mac ID#" column. Then, when…
-
Sheet Summary Field Formula to Summarize Expenses in a Column by Year
I have a sheet that includes a column of net costs with a Year column. I want to create a sheet summary field with a formula that calculates the total net costs for a specific year (e.g., 2023). I tried the following =SUMIFS(Revenue_Column, Year_Column, Year_Value) but kept getting an error, and I don't think it's…
-
IF statement using AND to generate colored symbol
Hello! Can somebody please help with the following formula? I am trying to generate the colored symbols (Green,Yellow,Red) based on the percentage of tasks complete along with the go live date. I am able to get a single If statement to generate the symbol but am failing at the AND one. Below is the formula that I'm using…
-
Formula to count the number of cells that contain 3 criteria including name within a list
I have tried a number of formulas and each time run into an error regarding the last item. I want to count a row if a name appears within a list in a cell with a designated date and location within that row. =COUNTIFS({Class Attendance Range - Date}, [DATE], {Class Attendance Range -Location}, [LOCATION], {Class Attendance…
-
How to I calculate percentage progress based on status?
Hello Smartsheet Community, I appreciate your support on calculating "% of complete" based on "Status" value. I have some predefined values for "Status" column and need to connect the "Status" to "% of Complete". For example: When Status="Backlog" --> % Complete=0% When Status="In Evaluation" --> % Complete = 25% When…
-
IF/OR Formula Error
I created a formula to populate a field based on the status of various other fields. I got it to work with two conditions (Approved or Declined), but when I add the third (Submitted) with the false response of blank (""), I get an error. Please see the formula below - I assume I messed up something really easy :)…
-
Average CTR by Type of Ad by Year with DD/MM/YYYY Format
I'm looking to average the click through rates (CTR) by type of ad for 2023 in the SmartSheet Summary Sheet section. There is data from 2022 and 2024 on my sheet, so I'm trying to make sure the data is only calculated between 1/1/2023 and 12/31/2023. I'm using the AVG and COLLECT formulas and keep getting errors. Any help…
-
How to get a column to state a status off the health color indicator
Hi, I have a column with colored health status of some project tasks, due to the fact in email updates and that you cannot see the colored got in the email, I am hoping to add a column that with align with the colored dot column with words so those getting the email can see the health if the item. So just looking to have…
