-
What formula to use in Status column based on % Complete column?
Hi, I'm stumped...I've tried this formula multiple different ways and nothing seems to work. Status column: Not Started, In Progress, or Complete % Complete column: any number (column is set to %'s) This is what I need: IF % Complete = 0, Status=Not Started, IF % Complete is greater than 0 and less than 100, Status=In…
-
I am writing a column formula and get invalid operation error
I am using this formula to calculate % of task time but get invalid operation error. Task time is the amount of time spent doing a particular task divided by the shift hours (9) =[Total Task Time]@row / 9
-
Need IF Statement to return first two digits when first digit is 0
Hello, I need a formula that will return a city name based on the first two digits of a four-digit code. However, it comes back with a #INVALID COLUMN VALUE error if the first digit is a 0. For example: 01 = Louisville, 02 = Tampa, 03 = San Antonio, etc. If the number starts with 1, such as 10, the formula works fine but I…
-
How do i create a formula to source information from another sheet with multiple countifs?
I am trying to setup an automated metrics sheet from many other sheets and I am stuck on the very first "reference another sheet" formula due to the conditions (countifs) that I'm trying to get it to pick up. Here's the example: The metric sheet is trying to count KPI's from other sheets, for this particular instance a…
-
Index Match (?) to two different sheets (unique values)
Hi - I am not sure what is the correct formula to use - I have tried a few and I get errors, so would appreciate some assistance. I have been using Cell Linking to pull in the data, but due to the volume it has become cumbersome and unreliable. I have a master project plan with unique Site IDs in the primary column, and…
-
Copy the value in a cell to another cell - Dates
I have two columns in a sheet where I want to same date in both columns. I want to link the second column to the first column so that whatever date I put in that first column reflects the change in the second column. I understand this isn't possible with automation at this time as I don't want to date stamp it. How would I…
-
Requesting assistance with multipart COUNTIFS formula
Hi all, My team and I currently use Smartsheet to track monthly referral information. At the beginning of each month, we are looking to obtain the number of referrals received for the prior month (ex: because it is January, we want to capture data from December). Here is what we are trying to track: How many "SEN"…
-
Trying to include two bits of criteria from the same column in a COUNTIFS formula
I have used a variation of this formula so I can compile the total amount of stores in two particular states. I was able to exclude both NY & MA in a similar formula using the <> before {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, but now that I ONLY want to include MA & NY I'm having trouble. I'm able to do one state…
-
How do I count multiple types in a column?
This is my current formula that works: =COUNTIFS({STATUS}, "Completed", {AUDIENCE CATEGORY}, HAS(@cell, "All Employees"), {QUARTER}, HAS(@cell, "Q1'23")) But now, I need the formula to count multiple quarters (see the last part of the equation). I need it to pull Q1'23, Q2'23, Q3'23, Q4'23. How do I add those on? ... Thank…
-
What's the purpose of ISBLANK or NOT(ISBLANK
Pretty simple and quick question. Why would I use an ISBLANK formula, instead of just adding ="" in a logical expression? Or why would I use NOT(ISBLANK instead of <>"" in a logical expression? Maybe because it's easier to interpret if ISBLANK or NOT(ISBLANK is used?
