-
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…
-
INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA
Hi fellows, I've a Destination sheet (SHEET 1) and a Source sheet (SHEET 2). In SHEET 1 i've set the following INDEX/MATCH formula for all the cells in col. B: =INDEX({SHEET 2-Col. B}, MATCH(SHEET1-Col. A, {SHEET 2-Col. A}, 0)) that works exactly as the following VLOOKUP formula (n col. B): =VLOOKUP(SHEET1-Col. A; SHEET…
-
Add a date (first of month)
Hi there! We have a smartsheet that is for program enrollment. The program start date is the first day of the following month after the enrollee's start date (so, if they start on 11.15 in their office, their program start date is 12.1). I originally used: =DATE(YEAR([Net Add Start Date]@row), MONTH([Net Add Start…
-
Weekend date Formula in text/number column
Hello everyone, I am trying to create a table in which the first row has the week-end dates (week ending Friday), instead of putting the dates manually, is there any formula I can use? Please note that I need the columns to remain in text/number format only
