-
AVERAGEIFS
Good afternoon community, i am trying to transfer an excel formular into Smartsheet and i am having issues with the formular due to it being an averageisf formular. The formular: =AVERAGEIFS($R:$R,$S:$S,BC5,$D:$D,$BD$3) The logic: The calculation is: if both the indicator and the month (as a number) match, then calculate…
-
Children Rows of an Auto number.
I have the first column as an Autonumbered for requests. Some of the request will have sub requests and I am wondering if there is a way to create a child row without having a new autonumber generated.
-
Populate cell with selection in one of multiple columns
I'm very new to Smartsheet and I don't work with formulas often. I'm hoping there is a fairly simple formula out there to make this work, but I haven't had any luck so far. What I'm trying to do is systematically populate the "Funeral Director" column with one of the selections made in either the "AJ Holly Directors",…
-
Count if Container Code is Unique
Hello, I have a metrics sheet that is referencing another sheet to count how many counts of a specific Hold/MECQI Reason =COUNTIFS({Hold/MECQI Reason}, CONTAINS("incorrect container", @cell), {Hold Issuance Date}, >TODAY(-180), {Hold/MECQI Reason}, NOT(ISBLANK(@cell ))) I only want it to count if the corresponding…
-
Is there a way to link cells between sheets so data can be updated on either sheet?
I manage requests my organization receives with a main sheet and a couple of sub sheets. Specifically, a form is utilized to generate a new request line item on the main sheet. Then if certain criteria are met, the row is copied to a sub sheet. Unfortunately, the information in the copied row does not update if data…
-
Sort Formula issues
Hi, This formula causes a #unparseable and can't figure out what is wrong: =IF(Description@row = "Overall Project Risk", "0-00", IF([RAID Type]@row = "", "", IF(Status@row = "Complete", 7, IF([RAID Type]@row = "Decision", 1, IF([RAID Type]@row = "Issue", 2, IF([RAID Type]@row = "Risk", 3, IF([RAID Type]@row = "Action", 4,…
-
Countifs Question
I have a sheet that has 2 date columns, 1 is the date we scheduled an activity, the 2nd column is the date that it was actually completed. I need to count the number of times these dates are the same broken out by month. COUNTIFS([Date1]:[Date1], [Date2]:[Date2], [Date1]:[Date1], >=DATE(2023, 1, 1), [Date1]:[Date1],…
-
Formula for Status for Upcoming vs In Future
I am trying to auto update the status column based on the Due Date and Date Completed Columns. =IF([Due Date]@row = "Not Enrolled Yet", "Not Required", IF(ISDATE([Date Completed]@row), "Complete", IF(AND([Due Date]@row < TODAY(), [Date Completed]@row = ""), "Overdue", IF(AND([Due Date]@row > TODAY(), [Date Completed]@row =…
-
Locking a cell after running formula or deleting a formula after it has run
Hello all. I have a bit of an issue, I wrote a formula to enter todays date after a checkbox has been checked in a cell. This works great, however if I uncheck the box the date disappears. I need the formula driven date cell to lock or hardcode the date after the formula has run once, automatically. Current formula:…
-
Can you use the HAS function in a COUNTIFS formula?
Based on what I read about HAS Function in the Formula Basics (HAS Function | Smartsheet Learning Center) I should be able to use HAS with COUNTIFS. When I try to use it in a sheet, I receive the error message: The formula cannot be generated because the HAS function is not supported in COUNTIFS. The formula I tried is:…
