-
VLOOKUP/IF or INDEX/MATCH?
Hello, I'm not sure which combination of formulas I require (VLOOKUP, IF, INDEX, MATCH...) In sheet 1 I have the columns "resource name" (text) and "onboarding paid?" (check box) In sheet 2 I have the columns "resource name" (text), "onboard?" (check box) and "milestone paid?" (check box) If the resource name matches, AND…
-
Sheet Summary - SUM Chilldren Rows based on Status and Dates
Good Afternoon! I am trying to sum children rows where certain status' exist where the Pick-Up Date is today, within the next two days, and in the future beyond that. (Really, 3 formulas). I tried applying the following formula before involving dates to make sure the first part of logic is correct but it is giving me an…
-
Date Range Formula
Hi, I'm trying to set up a formula to count the number of requests that were submitted between a specific date range. Below is the formula that I've written, however I'm still receiving an invalid operation error. Can anyone help me with this? =COUNTIFS({Citi Table Change Tracker Date Submitted} >= DATE(2019, 1, 1), {Citi…
-
Can VLOOKUP match on the most recent row of duplicate entries?
I know my question itself is confusing, so let me explain. I have configured data to come into Smartsheet from SharePoint via Power Automate. It is depositing some basic metadata into a "From SharePoint" sheet (image included here). I am then doing a VLOOKUP against this sheet to populate some of that metadata on a master…
-
Checkbox to equal 100% in % complete
I am attempting to do the following. If the "Completed" column has a check, in the checkbox, I would like the % Complete column to read 100% Here is the formula I thought would work using task 4.of the project. =IF(Completed4 = "Complete", "100%")
-
Best way to auto Sum cells with both numbers and text?
Hi everyone - So i am trying to find a way to sum values in cells containing both number and text like "1 Macbook Pro, 2 Lenovo Tiny PC, etc. and give me the total for each item type in the totals column. Please see the screenshot of my example smartsheet. All of the column values are drop-down list of things we usually…
-
Formula to calculate two week iteration
Our company works in two week chunks we call iterations. Using the following formula in Excel I am able to calculate what the current iteration is. However when I import from Excel Smartsheet doesn't appear to like the formula. Using the current date the formula in Excel is =FLOOR.MATH(DAYS(NOW(),"6/11/01")/14) Using a…
-
Updating Date Column if Another Column has been Changed
I am trying to create a formula to change a Date column to update to the current date when another column has been changed. I have used the TODAY function within IF but this updates the current date every time the sheet is opened and saved. I only want the Date cell to change when a specific other cell is changed.…
-
Using a formula to Reference another sheet with two variables
I'm trying to create a formula for a report that references another sheet but it needs to reference and check two columns. My crude formula below to illustrate: =COUNTIF ((RefSheet1Range1)="Windows Server" AND (RefSheet1Range2){Status}="Completed") This would return a count of the number of Windows Servers that have been…
-
Ignoring Blank Cells
How do I make this formula ignore blank cells? I want it to total the percent of YES cells to NO in a range. =COUNTIFS(CHILDREN(), OR(@cell = "YES", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))