-
Is there a way to weight the average in a sheet summary report?
I have a report that pulls from the sheet summaries of multiple sheets. One of the fields divides two other fields to show the % of items completed. However, when grouping & adding summaries to each column, the % completed summary averages the values below it rather than showing the actual % of items completed. In the…
-
What formula do i use for date ranges = Priority?
I have a priority column I have a invoice date column I require a formula that changes the priority: to high for anything over 90 days old of the invoice date To medium for anything between 61 - 89 days old of the invoice date To low for anything 0 to 60 days old from the invoice date can anyone help with a formula and/or…
-
Is there an automation/formula that allows a copied row to be updated?
I have created a master sheet for grant invoicing. We have 14 different institutions who received the grant and who will be submitting invoices to the master sheet. I have created a workflow for each institution so that when they submit to the master sheet, that row will be copied to a separate Smartsheet with just the…
-
Calculate Average of Task Progress, per Person
Hello, I am trying to create a dashboard showing the average task completion status' of a number of team members. I feel this needs to be completed in Sheet Summary, but can't figure the right formula... Essentially trying to get; AVG of Column [Status], IF Assigned to [Joe] Any clues? thanks PS. the dark rows are parent…
-
Formula to display multi-select options separately
I want to use the same formula, but instead of adding number amounts, I want it to display multiple selections in another column. Currently, tried this: =IF(CONTAINS("- Joining neighborhood-based, in-person adult learning house parties", [22/23: Torah (Learning) Commitment]@row), "Jewish Journey") + IF(CONTAINS("-…
-
Add 3mth date increment to 'date completed'
Hello, i'm hoping someone can assist me with a formula that i'm entering which returns an invalid error. I have scoured the help forum for assistance, but not had any luck replicating the formulas in my sheet. What i want to do is automatically generate a date which is 3mths after the date entered in the 'date completed'…
-
How to exclude a column formula on certain rows in a specific status
I am trying to capture when requests are on time vs past due in the 'Current Status' Column, and also trying to capture how many days since a request was submitted in the 'Days since Req Created'. I have those formulas figured out. However, I don't want those to run when the 'progress' is completed. So how do I add that in…
-
IF statement - Less than 3 weeks from today
Hello, I am looking for help on a formula for IF([Proposed Effective Date]@row is less than 3 weeks from today display "Rejected" AND IF([Have you received quotes form competitors?] = Yes then display "Rejected" So if one condition applies it displays "Rejected" or if both then "Rejected" displays as well. Thank you so…
-
Matching Neighbor Cells or Rows to Most Recent Date
Hey All, Background: Im trying to build a fluid dashboard that tracks information coming in from a form. This form is then routed to several sheets where I break it down further based on certain criteria. I am looking for a way to report on the most recent metrics in the given sheet using the sheet summary feature to make…
-
CountIfs with Distinct and a date range
I am sure it is doable, but I continue to struggle to get CountIFS, AND, and DISTINCT to work together. I need to count distinct dates in a long column and need to determine how many distinct dates there were in particular months. I'm able to use this to see distinct counts after a certain date:…