-
COUNTIFS/CONTAINS FUNCTION
Hi All, I need help with a formula to Count every instance a name appears in a multi-select row of a column based on criteria from another column first. At present this is my first formula: =COUNTIFS({CORP ADMIN TEAM TASKS Range 1}, "In Progress", {CORP ADMIN TEAM TASKS Range 2}, "Jane Doe") However, it is only…
-
Cross Sheet Countifs multiple columns
I’ve built out an Error Tracking Mastersheet and I’m now trying to roll the metrics up but I’m struggling a bit. There are 4 types of errors that can be made (DE, CE, LE, EE). There are close to 20 people, but for the example below I only show 3. Each person is their own Drop down column with the same 4 error types. Each…
-
COUNTIFS Function of # of Values Used in a Median Calculation for Metric Sheet
I have a formula in a metrics sheet where I am successfully calculating the median of values that meet a specific date range: =MEDIAN(COLLECT({Resi - Financial Approval Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1))) I now want to calculate in a adjacent column the total…
-
Index/Match Cross Sheet References Broken after Control Center Provisioning
I'm having issues with Index/Match formulas to external sheet being broken on new sheet created through provisioning in Control Center? I have a different blueprint set up in Control Center that contains an Index/Match formula to an external sheet that works as intended and I can't figure out what is different The external…
-
Parent Child formula for Checkboxes
Hello, I am working on a checkbox column formula for a sheet that will function as a roll up but will not automatically check the lowest children. Currently I have this: =IF(COUNTIFS(CHILDREN(), 1) = COUNT(CHILDREN(Tasks@row)), 1) That formula works, but you have to manually add it to each parent row. I would rather have…
-
NetworkDays with IF functionality
Hi, I am trying to gather a sum total headcount for each day of the month based off another sheet that has a date range of each users visit. How do i capture the total headcount for each day a user is onsite? I have been using IF(NETWORKDAYS( START DATE, END DATE, = specific day) and get an incorrect argument error.…
-
=COUNTIFS Function does not appear to work
Hi all, I am new to Smartsheets and would appreciate a little help. I have a sheet with a list of UAT defect reports within it and I am using a separate sheet to try to calculate various statistics. When trying to calculate how may defect reports are open for an individual I have used the following formula: =COUNTIFS({UAT…
-
Join Collect - Return Multiple Values from a Multi-Select Column
Hi all, I have a change log where the "Assigned To" column has multiple assignees - I have another column where I would like to return the department(s) of the assignees, which is pulled from another sheet. However, when I use the following formula, it returns a blank result when I put two people in the "Assigned To"…
-
CountIfs Formula
Hello. I am new to Smartsheet and having some trouble with the countifs formula. I have two columns I would like to use - department and status and have created a sheet just for the formulas (to be used later on a dashboard). I would like to display the count of each status for the department (i.e. IT has 3 in progress and…
-
% Complete Based on # of Tasks Assigned
I need a Smartsheet formula that will calculate the percentage complete for a selected cell based on the count of values in the same row in column "Assigned To (Team)" and the count of value "Complete" in the same row. We have 3 teams that complete separate tasks for one project, which is created as a new row from a form.…
