-
Count of cells in a date range
Hello, - I am fairly new to Smarsheets and not very knowledgable past basic formulas. I am trying to create a formula that will display the count of cells whose date is from Monday through Sunday, each week. Like how many open items were closed this week. I can't seem to figure out the criteria wording....…
-
Using Multiple IF Statements
Hello, As Smartsheets does not allow for multi-select dropdown lists, I am trying to work a way around this using checkboxes and IF statements but keep running into a roadblock. I want it to say: If the "Chemotherapy" checkbox is selected in Column A, Column E says "Chemotherapy" . If the "ICU" column is selected in Column…
-
Index Match with 2 sheets
Hi, I have successfully setup an index/match with 1 sheet but I need to add a 2nd sheet for the index to search as well. How would I set this up in the formula? I tried to notate both sheet's columns for the range but keep getting an error. Thanks
-
Count children & grandchildren of multiple rows under main parent
I'm trying to count the grandchildren status of multiple parent rows, and show a summary under mainparent (binos/mgmt/dmi). I'm using fx="Complete (" + COUNTIF(CHILDREN(), "Complete") + "),In Progress (" + COUNTIF(CHILDREN(), "In Progress") + ") , Not Started(" + COUNTIF(CHILDREN(), "Not Started") + ")" to get the summary…
-
Cell value is cleared easily for a locked column before unlock
Hi, I have a calculated column field and do not want others (including myself) to override the calculation. So, I made the column as lock column. However, I was able to clear the calculation or value in the column without UNLOCKING the column. My expectation is: - others cannot change the cell values in this column; and -…
-
COUNTIF
Hello and good morning Smartsheet community, I know you can use the =COUNTIF in order to count the number of times a (SINGLE) name or word appears in a range of cells. My issue is that I am trying to count how many times the word safety is used in a range of cells where the word safety is used in a sentence and not the…
-
Add up all PAST DUE Items
I would like to add up all the action items on my Action Item log that are Past Due so I can show this on my project dashboard. How do I construct the COUNTIF statement to achieve this? Thank you from the newbie!
-
Formula Help
Hey, nested IF statements are my downfall so I am hoping someone can help me. I have a column entitled "At Risk" and it's formula is this - =IF(AND([End Date]5 < TODAY(), NOT([% Comp]5 = "1")), 1, 0) It puts a flag in if the % Comp column is not marked 100% and the end date is less than today. However, there are some items…
-
Auto populate from one sheet into blank rows on another
Hi All! I searched for this, but couldn't find an answer. Maybe it doesn't exist! I'm trying to set up a new dashboard for sales reps. In one sheet I will have them fill out all of their activities. I was TRYING to get them to fill out all information on one sheet to keep things simple for them. Is there a way for me to…
-
Unexpected behavior. (Found the Issue)
[Update: I've found the solution and posted it in a comment] I am having some issues with a very complicated set of sheets I have created. Please be warned, I am stressing the limits of smartsheets, and it is a rather complicated group of formulas. That said, information is below. Background: I am trying to optimize a set…
