-
Countifs with criteria on 2 different ranges
hello, I have formulas in a metrics sheet looking at information in a second sheet. I want to count the number of items in the second sheet that meet both a criterion of a checkbox being checked in one column (SS range 2) and the value in another column (SS range 3) being between 2 values (in this case, between 0 and the…
-
VLOOKUP with Multiple Criteria or should use INDEX or COLLECT?
Hi! I'm trying to do a multiple criteria look up and I'm not sure I'm going about it with the correct formula. I have a Project ID referenced on a project profile, then the formula needs to find that project and display the data, easy with a vlookup. Here's where it gets fun, I need it to look at the next unique time entry…
-
Ranking without Duplicating Ranks
I used the RANKEQ function for the first time recently and realized that any identical values will have the same rank. It will skip however many duplicates you had in the ranking numbers, so you may have a ranking scheme that looks like 1, 2, 3, 3, 3, 6, 7, 7, 9, 9. This may be useful in some situations but it is actually…
-
Net Days Between Due Dates, Referencing Current Row and Row Above
I am hoping to identify a duration between dates on my sheet but would like to reference the current row and row above in the formula. I created two helped columns to identify Row # - auto number column "Auto" and a "Row #" column with the following formula =MATCH(Auto@row, Auto:Auto, 0).
-
If and Question for At Risk Flags
How do I make this work (combine into a single statement)? =IF(ISBLANK([Days Overdue]@row), 0, 1) AND =IF(AND([Days Overdue]@row > -5, [% Complete]@row < 1), 1, 0) New to smartsheets! Thank you.
-
Function takes data in one cell and sent it to another cell?
Hi, Column A has function code in it that finds data I need and put it in a cell. Column B Column C I want to put a function codes in Column B that takes the data from Column A and puts it in Column C. I have a sheet full of function codes and connected to contacts and more I don't want to rewrite. This would be the quick…
-
USING FORMS WITH pARENT /CHILD ASSOCIATIONS IN SPREADSHEET
Greetings, Below is a screenshot of a spreadsheet that has parent / child associations. I would like to create a form that will allow team members to input the various meetings & Times, they have during the week. 1-can smartsheet automatically put the response in child rows when a parent row( week Day) has been selected?…
-
How to use multiple if not or statements
I am receiving an "invalid data type" result when I run this: =IF(NOT(ISBLANK([Start Date]@row)), NETWORKDAYS([Due Date]@row, IF([Date Completed]@row <> "", [Date Completed]@row, TODAY()))) and there is a null value for Due date. How can I add OR statement to this so that if either Start Date OR Due date are (not) blank…
-
Average Guest Check ($) When Some Cells are $0.00
Hello! I'm trying to get the average of a data set IF the cells are greater than $0.00. Unfortunately, the formulas I've tried have all come up as #UNPARSEABLE. I've included an image of the data for reference as well as the formula I tried based on the syntax suggested. Any suggestions? It might also be worth mentioning…
-
Count of a column between two dates
I'm trying to determine how many times we post for a hospital in each year. (Ex: ABC Hospital posted # times in 2022). The formula I'm using now is: COUNTIFS({SS Hospital}, [Primary Column]@row, ({SS Posted}, >=DATE(2022, 1, 1), {SS Posted}, <=DATE(2022, 12, 31))) SS Hospital references the column from a different sheet…