-
Count Rows in Sheet Meeting Certain Criteria
I'm trying to get a count of the number of rows in my sheet that meet a certain criteria and am getting an #INVALID OPERATION error. Here is what I have so far: =COUNTIF([Resident (Lookup)]:[Resident (Lookup)], AND([Resident (Lookup)]@row, Status:Status <> "Closed")) What I'm trying to do is get a count of the number of…
-
My Syntax is correct but I get invalid refernece
The following formula appears correct in that I want to the correct response only if the UPN column is populated and the Construction column cell 1 is Greenfield, one answer if construction column cell 2 is Brownfield then index the exists column to see if the closest parent already exists. if so return "existing" if not…
-
Status Column Formula
Hello Team, can you help me with status column formula to automate status column: Green = "% complete"=0% Blue = "% complete"= 100% Yellow = "Finish Date" >Today() but < Today () + 3d Red = "Finish Date" + 3d Remaining "Green" I was trying but unparseable: =IF([% Complete]@row="100%", "Blue", IF[% Complete]@row="0%",…
-
INDEX/MATCH or VLOOKUP for Date Range
Hi, 1) I have sheet "X" set up as shown in screenshot 1 below where I have got a start date and end date column and the week numbers associated for the date ranges. 2) I have got another sheet "Y" set up with empty week number column as shown in screenshot 2. Please suggest me with a formula to enter in the Week number…
-
Convert Cells into a Formula Columns Issues
Hello, I'm getting the below message when I'm trying to convert a cell into a formula. The sheet will receive its data from a form, except for the cells I'm converting. The column formula syntax isn't quite right, see our help article https://help.smartsheet.com/articles/2481944?ss_lc=en_US&source=apphelpicon for more…
-
IF(AND Invalid Operation Error
Hi! I need help fixing this formula - keep receiving an Invalid Operation error. I'm trying to return "No" if an email from the row in the Smartsheet file matches an e-mail from the cross-sheet reference's file in the E-mail column and that cross-sheet reference's row also contains "Mgmt" in the User Role column. (So two…
-
Counting help
Hi There! I am trying to create a reference sheet to pull data for a dashboard and am trying to figure out how to count instances a item with a certain value is check off. So, how many items checked off of the list are for "Property Info". I've tried using COUNTIF, COUNTIFS, COUNTM and so on, but I can't seem to figure…
-
Unable to add up duration for metrics calculation
I am trying to use cross-sheet formula to add up the planned duration for certain workers, for performance analysis purpose. After setting up the Actual Start and End date, we copied it to the Planned duration, Plan start, Plan end - as a baseline reference. When I am trying to calculate the Planned days by employee, it…
-
I am in need of a formula that will count the number of entries in one column like below.
I would like a column formula to auto populate column two "Total number of entries in left column" does anyone have a suggestion on how I can do this?
-
Mode of Column (text)
Hello, I have seen that Smartsheet does not have a mode function similar to Excel, but I would love some help in figuring out a workaround. I have read other posts about workarounds, but have not had any luck getting it to work. I currently have a lot of 5-point Likert survey data (across 60 "parent" surveys) and I want to…
