-
Using COUNTIFS with multiple conditions
Trying get a count of issues in a catergory sheet that is referenced from a main sheet and then add another condition to count the categories of only the ones where the site is checked. The first COUNTIFS works, but not the 2nd one like below . =COUNTIFS({Day 1 Support Log Range 1}, =[Issue Category]1, {Day 1 Support Log…
-
Networkdays and #invalid data error
Hoping you can help with the below formula. I need to return zero and can not get the iferror formula to work. current formula is =NETWORKDAYS([Agreement Effective Date]4, [Actual Go Live]4). Screenshot included for reference. Thanks, Heather
-
If Statement Comparing Date of Text Entry Between Columns
We work from a running tracker were there are a series of dates (entered as text, not an actual date) are entered into different columns to signify when certain steps have been completed. I am trying to find a way to automate a flag or alert or something to designate how many days go between each step so we can easily see…
-
Count and compare date columns in 'form' based worksheet
I would like to know how I can count number of activities (rows) which are completed ('finish' date column) at or before 'planned finish' date. In the same formula it should only be counted when 'complete' column is ticked off and empty cells should not be counted. Note that this is a 'form based' worksheet, meaning rows…
-
Look up blank cell and add a value in another column on the same row
Can someone show me how to write a forumla that will look up if a cell in the column "Critical date" is populated enter 1 in column "CD2" on the same row. Essentially what I'm trying to do is run a report that summarises all my critical dates but I can't select that date column in my report. If there is a better way I'm…
-
Formula Assistance - Duration of Activity at Specific Rate
I hate math, so am asking the Community for some help. Looking to build what I think should be a reasonably simple formula for someone that doesn't hate math. Here's the Word problem: We have equipment that scans files for malware at the rate of 77,000 files per day. Apparently it doesn't matter if the file size is big…
-
IF formula or Nested IF
I'm trying to create a formula using the Flag symbol and a % Allocated to Project column. I would like the flag to raise if the % Allocated to Project column is 50% or less. If greater than 50% or Blank I would like the flag to stay down. I thought I could accomplish this with =IF([% Allocated to Project]@row< 0.5, 1, 0)…
-
SUMIFS for multiple criteria
This should be fairly simple, but I am spinning on this syntax. Trying to sum my "Invoiced" column if the Year Column = "2019" and the Type Column = "Expense". I have tried a few variations, but this is what I think should work? =SUMIFS(Invoiced:Invoiced, Type:Type, Expense, Year:Year, 2019). I have tried quotations around…
-
Graph 3 months running totals
I have a worksheet that returns the count of requests for a given week, and I have set it up to work for a year. What I envision is creating a graph that illustrates a running three months worth of requests at a time. I would like the graph to automatically update so that the range always displays the last three months. In…
-
Traffic Lights/cell colour based on corresponding numeric value
Hi, can anyone please help? I want to set up an alert system in SmartSheet that turns a cell green (and labelled 'on track') if a corresponding cell is >90%, yellow ('needs work') if 70%-89%, orange ('at risk') if 40-69%, red ('behind') if 2-39%, and blue ('not started') if <2%.
