-
How can I count distinct values under a same month?
Hi everyone, I currently have a data set for which I would like to count all unique values that are under a same month and a same year. For example, the following table shows two duplicate values (TR ID's) on two different dates. In this case I would only like to count the unique TR ID's for the month of August, 2020…
-
Excluding two values for Sheet Summary Formula
Hi all, I was wondering whether you might be able to help. I have a column "Status" and I would like to write a formula for a sheet summary field that counts the number of status' that are anything other than "On Hold" and "Complete". I've tried various COUNTIFs but none seem to be working! Any help on this would be great!…
-
Formula Error
Hello, I was hoping to get some help with a formula. We are using INDEX/MATCH. Its all working great when we are referencing one column. When we change the reference to select a range of columns, the formula doesn't work and we get #UNPERISHABLE. Can someone help us figure out how to reference a range of columns? Here is…
-
Adding text to a field, then changing text color based on calculated values
Hello. I would like to ask if anyone has had an opportunity to have text added to a column, then based on a calculated value, change the text color from say black to red based on a calculated outcome. For example. I have a checkbox that simply determines if multiple Techs were required for a specific task. If the checkbox…
-
Sheet Summary CountIFS + Date Range
Hi All, Need some assistance. Found a great post & this on the subject but was not able to get my formulas to work. Hoping the group can help. I have 1 working the other is not. Background: Form was converted over from Google Form/Sheets & several formatting changes had to be made in August. (Probably issue #1) Co-workers…
-
Auto-populating a range of columns with a number based on dates in 2 other columns.
Hi, I am having difficulty figuring out how to auto-populate a range of columns based on dates from 2 other columns. For example.. A contract's start date is Jan 2022 and its end date it October 2022. The value of the contract is 10,000 USD. I would like to populate columns Jan 2022 through October 2022 with 1,000 USD in…
-
SUMIF row is in the next 5 working days
Hello, I am trying to create a formula that counts the total size (EQA) of the jobs that are due in the next 5 working days. I am using this formula but it's coming up with 0 =SUMIFS(EQA:EQA, [Delivery Date]:[Delivery Date], ISDATE(1), [Delivery Date]:[Delivery Date], NETWORKDAYS(TODAY(), TODAY(7), 0)) Any help on this…
-
Conditional formatting based off of blank date cell
Hello! I have tried to solve this problem with a lot of experimentation and reading many posts in the Smartsheet Community. Unfortunately, I just can't get it to work. I am trying to change the color of the circle symbol of a status field based off of the value in a date field (Target Date). I want the symbol to turn gray…
-
Collect and HAS Functions
I need to find the average dollar amount across a range only if the fiscal year IS 2018 and a multiselect column HAS a given value =AVG(COLLECT([Funding Amount]:[Funding Amount], Subsector:Subsector, HAS(@cell "Cell Therapy"), [Fiscal Year]:[Fiscal Year], "2019")) Is what I'm trying to do feasible?
-
Formula creation to auto-populate priority symbol
Hello, I'm looking for help with creating a formula. I'm trying to auto-populate the "Project Heath" column (red, yellow, green symbols) based off of my "Provider Start Date" column. If "Provider Start Date" is in 15 days or less from today, the "Project Heath" should be “red” If "Provider Start Date" is 16 days to 34 days…