-
Sumifs formula only if all columns contain a value
I have sheet that is "scoring" projects across 6 criteria. The criteria all have numeric values associated with them from 1-4. I want to sum the values for all the criteria, but ONLY IF all columns have a value. Meaning, if someone fails to score one of the criteria, I want the sum column to be blank vs it summing the…
-
Data Point on Dashboard that updates based on current month
I have a list of tasks for the whole year. I would like to create a COUNTIFS formula in a cell that calculates the number of tasks completed in the current month. I've got a formula column that defines the Month # and cells for counting the completed lines in each given month, but not sure how to surface only the current…
-
Countif specific type and year
=COUNTIFS(Type:Type, ="CTA New"), ([Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = 2022) I've been looking at examples of this question all day - but for some reason I can't make my formula work. The above formula is #Unparseable. I want to count the number of rows with "CTA New" Type and 2022 Date Submitted…
-
Automation - Condition Path - Change Cell Value
Hello! I have a series of automations that change a number of different cell values (check boxes) to Checked if the conditions are met. The issue that I'm limited to changing the value in 20 columns, but I need it to change 30+ columns given the specific conditions. From what I can tell, this is only an issue in Condition…
-
Rolling 12 Months using AVG COLLECT formula help
Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help. =ROUND(AVG(COLLECT({Data days from…
-
CountIFs using multiple criteria
I want a count of the number of tasks that are complete. I don't want to include anything marked as not applicable or any headings (Hierarchy=Grandparent or parent), and it is counted as complete whether it completed by Medhost or by facility. =COUNTIFS([Not Applicable]:[Not Applicable], 0, Hierarchy:Hierarchy, "Child",…
-
How to return multi-select values using INDEX/COLLECT
Need the Smartsheet fam expert knowledge. I am trying to return a value (persons name) based on the hospital dept they work in. I created a reference guide crosswalk with name in one column and the name of the dept in another. There are multiple names that work in the same dept FYI. I import hospital data and am trying to…
-
Help with milestone tracking formula
@Paul Newcome Hi Paul, I've sourced many of your formulas in the past, but haven't found one that can help me with the following. I need help with two formulas please. We have a sheet that needs to track when Members hit milestones for participants. For every Reporting Period each Member submits their Cumulative…
-
Set filter to only show certain columns
Hello! I'm curious if a way has been found to only show certain columns, regardless if its implemented by SS or a work around. The columns don't need to be automatically filtered by a specific criteria, but more if I could hide certain columns and have the sheet save that sort of setting for when I need it again, so I…
-
IF(JOIN(COLLECT Statement
Hello! I am using this formula to collect a set of ID numbers based on a criteria. =JOIN(COLLECT([MED ID 2]:[MED ID 2], [Duplicates 2]:[Duplicates 2], >1), " ") This gives me exactly what I need, but I want to wrap it in an IF function so that if the criteria is not met, the cell will say "No" This is what I've been trying…