-
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?
-
Sum column if another column is not blank
Hello all, I am trying to create a formula that will only sum budget cells that have a date in another column, if the date column is blank then those budgets would be excluded from the total sum. I'm having a hard time finding any formula that will work, possibly because these cells are linked from another sheet? Any help…
-
Why does my Metrics constantly stop calculating correctly?
I've been making a "Metrics" sheet for some time now, and in the beginning everything was calculating fine. But it seems to be when I add more "data tables" if you will, or more metrics, some tables stop calculating correctly. Here is what my sheet looks like for a better understanding So, the first few-ish sections will…