-
Ranking within hierarchy (=RANKIF)... using CHILDREN/PARENT/ANCESTOR instead of COUNTIFS
Hi! I want to create a ranking within a hierarchy, here is a attached a simplified example of what I'm trying to achieve (my real sheet is 200+ lines and has a 5-level hierarchy). Right now I'm using this formula (in column Rank hierarchy 1) =IF(Hierarchy@row = 1;COUNTIFS(Hierarchy:Hierarchy; $Hierarchy@row; Value:Value;…
-
Summing a summary sheet report
Hi all, I have built a sheet summary report, and this is showing the data that I would expect. I would like to be able to sum each column - (i.e. one of the columns is no of reports that are live) so that I can get a total, and to put this on a Dashboard either as a Metric or have 5 or 6 columns combined in a chart. Sorry…
-
How to calculate values in a row?
Hi, I am just new in smartsheet, how can I calculate values in a row ? Please help. Thank You!
-
If, Then Formula Needed
I want a formula to check if an @row cell is blank. The cell referred to is circled in red. If it is NOT blank, I want a default value populated in the cell with the formula (circled in green):
-
Is there a way to use a formula to tell my contact cell to populate based on another field?
So, I have my team member names populating in the sheet with just their initials - but I want to be able to tell the sheet that if those initials are in the "team" field, then add this matching contact to the "Assigned to" field. Is there a way to do this? I can drag the names down, but just was hoping for an automation
-
SUMIF Criteria met
I'm trying to calculate the sum of the numbers in a cell when the cell meets a criteria range. This formula works fine for providing the number in cell that is greater than 30. But what if I want the sum to be the total of the numbers in the cells that are equal to or greater than 25 and less than 30?…
-
Formulas not carrying down to new rows
I have a sheet with formulas in most of the columns that should be carrying down to newly added rows but after several months of it working correctly, they no longer carry down. I have checked for blank rows and made sure the formulas are correct in each column and present in every row but after working for awhile, they…
-
Traffic light formula
Feel like this should be a relatively simple formula, but can't get it to work! I want to do this: If content due date is greater than 4 workdays from today OR if content submitted is checked return green If content due date is between 1-4 workdays from today and content submitted is not checked return yellow If content…
-
Why is my multiplication formula invalid?
I am trying to create a column that multiplies the total hours by the pay rate of certain staff. In the pay rate column I have used the formula =IF(Name1 = "Sophie", "25.54", IF(Name1 = "Peta", "55", IF(Name1 = "Stephen", "55"))) This formula works well, however if I then try to use those values in another formula is just…
-
Using COUNTIFS to count specific words
I have a sheet with a multi-select containing topics such as: Age, Disability, Wage-Hour, Sex, Sexual Harassment, Sexual Orientation, and other unique values. I am using this formula: =COUNTIFS({Drop1}, CONTAINS(Claim@row, @cell), {Month}, 1) to count specific instances by month and it works except for Age and Sex because…