-
Struggling with a SUMIFS date range formula
For this use case, I need to sum the total hours (Range 3) for every entry that has a date between January 1, 2021 and January 31, 2021 (Range 1). The formula I have seems close but is still coming up as Unparseable. Ideally, what I want is an evergreen formula that can be applied to every calendar month in the year. Here…
-
Another status light formula question
Hello all, I'm having trouble with my status light formula. I have the following columns: Date Due % Complete Status (red/yellow/green/gray) This is my current status formula: =IF(OR(ISBLANK([% Complete]@row), ISBLANK([Date Due]@row)), "Gray", IF(AND([% Complete]@row < 1, TODAY() > [Date Due]@row), "Red", IF(AND([%…
-
Null value for COUNTM COLLECT
I am trying to return a null for any zero value for a chart but I get an incorrect argument set error when I try to add an IF statement. My original formula is a COUNTM(COLLECT) to count multiple entries from a multi-select dropdown that are NOT a certain value for several individuals for each month. How would I accomplish…
-
CountIfs X does not contain...
I am trying to get a formula to function and am having trouble. =COUNTIFS({KBeaver Workload Range 1}, "Complete", {KBeaver Workload Range 3}, "1", {KBeaver Workload Range 4}, NOT(CONTAINS("E:", @cell)), {KBeaver Workload Range 4}, NOT(CONTAINS("M:", @cell))) This is attempting to count tasks completed in Q1 by looking for…
-
Sheet Summary Formula
Asking for help again with a formula. I need to create a formula in Sheet Summary that rolls up how many participants from each of the 10 columns above attended which "Program Type." Program Type is a dropdown. For context: How many Blue Ridge participants attended "PSUDS ECHO." Wondering if the structure of the sheet is…
-
IF formula to pull biweekly updates
I'm trying to create a form that I send out to partners on a biweekly basis. I'm working with partners from 5 different countries, and I want their updates to be pulled using the sheet summary into a dashboard. Is there a way to do an IF formula with an INDEX formula to get the following results: IF [Country] is listed as…
-
Charts that show n= rather than percentage
Hi there, I'm trying to create a half donut chart on my dashboard that shows 5 stakeholders interviewed out of the total 15 that need to be interviewed. Is there a way to show this as a number (5/15) rather than a percentage (33%)?
-
Create Ranking Options in Smartsheets
I have three options available and I want individuals to be able to rank them in order of importance to them from 1 to 3. Is there a way to do this. ?
-
How to keep index/match value when source sheet is deleted?
Hi guys! Is there a way to keep the duplicated value in the new sheet even after the original source sheet is deleted? I use index/match to match the job order # on sheet A and sheet B, and sheet B has the work hours synced. However, since sheet A is generated every few hours and will be removed, sheet B will no longer…
-
Nested IF Formula for Flag functionality
My sheet has an End Date, Actual Date and Late column. The Late column shows a flag. I want to show flag red when the Actual Date is blank, but the End Date is today or in the past - AND the Actual Date is past the End Date Here is what I have so far, but I'm getting an INCORRECT ARGUMENT SET error: =IF(AND(ISBLANK([Actual…