-
Can I use VLookUp if what I am looking up is a range of dates?
I need to cross check an entry in my smartsheet with a range of dates against another sheet. I am able to do a Vlookup for start date (first pic below) but am stumped on how I can get it to look across the range of dates I need (start date to last date). I think I need to use another formula but am unsure on how to express…
-
Change values on TableA based on a change/correction request on TableB
Hi All, hope all is well. Currently I am working on an attendance tracker model, which will be used as a data table. The users will have predefined values on TableA for the whole year, which means that based on those everyone works every day during the whole year (which we know that isn't the case), so users need to submit…
-
COUNTIFS formula
Hello. Please help! I am attempting a formula where I would like to pull the number of 'Accomplishments' registered in a sheet in the 'last 7 days'. This is the formula I am using, but I am getting 'unparseable' - =COUNTIFS({Type}, ="Accomplishment", AND {Date}, >= (TODAY (-7))) Any help would be amazing. Thank you
-
RYG Ball Automation - Calculate Program RYG Health based on Project Health
Hi all, I'm looking for a formula to auto-calculate the health of a program, based on 4 components within each of the 4 projects that make up that program. Photo is below of how I have the projects & components set up, along with their RYG indication. You can see they "total up" at the bottom in Overall Program. Can anyone…
-
Check Box
Is there a formula or function that will check/uncheck all boxes in a column if one box is checked/unchecked. For example if top box is checked, it will check all the boxes in that column?
-
Sum is giving the wrong answer when adding values generated by "countifs" formula
I used Countifs to generate 4 numbers. I've validated these are correct with the data. I want to Sum these 4 numbers to get a total but it gives me the wrong answer. It should be 407. Why doesn't smartsheet give me the answer of 407? My formula in the Sum Total cell is =SUM([Total # Facilities]9:[Total # Facilities]12)…
-
SUM cells with formulas not working
Hello, I have a sheet with a column of cells with formulas in them. These formulas produce numbers. Then I have a second sheet, where I have a formula in a cell that references the first sheet's column with numbers. This formula in the second sheet is asking to SUM some of the numbers in the first sheet's column and divide…
-
Copy Row Automation causing duplicates
I am using an automation that copies rows which contain the word "submittal" into a sheet where we are tracking submittal dates for all our projects. I cannot use a report because we need to be able to publish to a calendar. Therefore, the end result must be a sheet. As most of you probably know, these dates can change…
-
Countifs for items completed YTD by quarter?
Hi everyone, first time poster here. I need a formula that will count Buy closings YTD by quarter. The best guess I have after reading other posts is: =COUNTIFS([Buy close date]:[Buy close date], > DATE(2021,01,01), [Buy close date]:[Buy close date] < TODAY, [Closing Q]:[Closing Q], Q1) It's not working but I can't quite…
-
Nested COUNTIF with AND
I am trying to use a nested COUNTIF formula with AND function. Column X - check if value is "1" Column Y - check if value is "2" Column Z - check if value is "3" I only want to see the count where all three statements are true. Help!