-
Counting Active projects within 2 dates
I have 2 date columns: Actual Start and Actual Finish in my intake sheet and want to summarize active projects by month over time. I setup trend sheet with months and years and want the formula to count actives for the month if: Actual Start is NOT blank AND Actual Start (Less than or equal to the Month/Year on the row in…
-
Counting cells with formulas
My underlying issue is that I'm trying to count cells that begin with a certain number. The data that is present in my SubCategory column is a picklist. Examples: * 106 COST BILLED IN ERROR * 110 FAILURE TO PURSUE PAYMENT AFTER INVOICE SUBMISSION * 201 CLIENT FAILED TO COMMUNICATE WITH FIRM * 207 PER CLIENT - COST IS NOT…
-
Check if Child Row
We use the formula =IF(COUNT(CHILDREN()) > 0, 1, 0) to check a box if a row is a parent row. This is a helpful tool when using conditional formatting to format rows which are parents, automatically. We'd like to do a similar thing if a row is a child row, but I cannot think of a way to do perform this logic. Has anyone…
-
Help with Formulas
Hello! I’ve been using Smartsheets for a while now, but am new to building them. I am having trouble working with formulas to identify current status of my tasks. I’ve searched the community and have tried out suggestions based on previous responses, but I’m still stuck. I’d be grateful for help sorting out the following:…
-
Prorated Rent Schedule Formula Help
Need a formula to calculate what is due per calendar year when the rent amount changes year to year (midyear) Any help appreciated! 06/01/19-05/31/20 Total Rent Due: $175,000 06/01/20-05/31/21 Total Rent Due: $200,000 Total Rent of 2019: Total Rent of 2020:
-
Using IF formula to set conditions for RYGB ball color
Hi there! I have been trying to set conditions with a formula to change the color of a RYGB ball based on two columns, the percent complete and the due date. I wish to use the RYGB balls and not just change the color of the cell, which is why I'm using formulas instead of the conditional formatting. I keep receiving the…
-
COUNTIF with Dates
I am trying to use COUNTIF referencing two Dates columns. The first "Due Date" column has dependencies. The second "Dates" column is simply a list of all possible due dates from the beginning of the project to the end (with no dependencies). I am trying to create a third column called "Total Due" that counts how many tasks…
-
Formula Help - Automating Status
Hi there, I am wondering if its possible to use a formula to automate a status column. For example, when someone submits an intake request through a form and a new row is added to my sheet, is it possible to have the status automatically change to "Intake"? Further, when someone approves the intake (i.e. Laura Approval…
-
Can't identify my Formula error
Can anyone suggest what is wrong with my formula? I get an #INVALID OPERATION result. I've looked at it for too long and can't find the issue. Thanks. =IF(NOT(ISBLANK([Order Date]1)), "Ordered", IF([READY TO ORDER]1 = 1, "Ready to Order", IF([CPM Complete]1 = 1, "CPM Need Approval", IF(NOT(ISBLANK([Vendor Quote requested…
-
Looking for Formula Help
In my sheet, I have two columns. One entitled "Hotel Check-In Date" and the other "Hotel Check-Out Date". So someone might input: Check-in 11/20/19 and out on 11/25/19. I know have individual columns for each date: i.e. 11/20, 11/21, 11/22, 11/23, etc. I am looking for a formula that will be a value of "1" in the correct…