-
VLookUP
Im doing a VLookUp (FX: VLookUp([Product Type]@row, {Product Type Assign PCM Range 2}, 2, False) Where the [Product Type] is a column that allows multiple values in the cell. When there is multiple values in the cell my formula will error out. But I want it to be able to give me both. The column that has this formula I set…
-
Contact column to return multiple email addresses
Good morning, I have a contact column, then an email address column where I use a Vlookup function to go to another sheet and pull the users email address from. However, when I enter multiple contacts I want a formula to return ALL those email address back to my email address column. I know VLOOKUP isn't it, and I tried…
-
Formula help to match multi select values
I have two multi select columns. Those columns have similar lists, but not exact. The multiselect data has associations with other data that we already know. (Washington = West, Iowa = Central, etc.) We’d like to get a formula that helps us identify when the West selections are input from the multiselect fields. For an…
-
COUNTIFS Current Year
Looking to count those with a Project Status of closed for those with an End Date in the current year. My formula results in a #INVALID OPERATION. Can someone assist me with what I'm overlooking? =COUNTIFS([Project Status]:[Project Status] = "Closed", [End Date]:[End Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) Regards,…
-
SUMIFS referencing another sheet
Hello, I'm trying to use SUMIFS formulas to create a summary grid based on another grid. =SUMIFS({Counter}, {Submission Date}, @cell = "", {Submission Status}, @cell <> "Waived", {Submission Status}, @cell <> "N/A", {Due in the next}, @cell = "1 Week", {TAT}, @cell = 3) The formula should sum the numbers in the Counter…
-
Count + Contains formula question
Hello! I'm having trouble with a formula in a sheet summary field. I have a column called "Service Type" where I want to count the number of entries that contain QA. There are multiple QA entries, some say QA+, some say QA5, QA10 or QA20, QA25, etc. to give you an idea. The common denominator is the QA, so I want to use it…
-
Check a box when Past due more the 1 year
New to this all. I'm trying to write a formula where if a date is more than 1 year in the past, it will check a box, but if no date is entered in a row the checked box will remain unchecked. I need to create a training log where not everyone is required to have the training. But certain training is on an annual basis.
-
Harvey Balls Using Formulas & Multiple Columns
Hi all! I am trying to determine what formula to use when calculating Harvey Balls using two different column details. Harvey Balls are in the "Corrective Action" column. What I want to do: Harvey Ball to be Full when all children rows are checked in "done" column AND there is text in the "Response" column Harvey ball to…
-
Set dropdown based on values in row
Hello, I'm trying to set values in a dropdown list using a formula (or any other suggestions) based on the columns in the same row. For example, I have a drop down list titled Cities that allows multi select with the values Chicago, Boston, Atlanta as options. I have three columns on the same sheet named Chicago, Boston,…
-
How to count unique values in different columns
Hello, I am using smartsheet to run an internal promotion for some phone agents. The promotion is they have to refer 15 unique customers to our YouTube channel to earn the reward. So, I need to count the number of unique entries they have. For example, if an agent name matches my name@row, I want to count the number of…