-
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
-
Formula Help
For project resource hours that I am trying to tally I am trying to look across multiple date columns, locating all the dates for a specific month, and then based on that, tally the total number of hours in multiple cells that pertain to those dates. I am not quite sure how to go about it. So far I have some up with (at…
-
using a COUNTIFS formula and @row
Hi there, I need assistance with formatting this formula: =COUNTIFS({Program Name}, (@cell), {Program Name}, [Program Name]@row)) My goal is to count the number of attendees per program session in our dynamic list. Given that the list is regularly updated with new program names, I'm seeking to count the rows based on each…
