-
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
-
Use Row@row to reference the row above in a column formula
I'm attempting to use Row@row along with an Auto Number column, in a column formula, so I don't have to manually update the formula when rows move within the sheet, but am not having any luck. The formula in the Est. RU Date column is RU Date, if it is populated, otherwise it should take the date in the Est. RD Date column…
-
Calculate quarter from date
I have a Date Ordered column and want to automatically populate a Quarter column based on the date ordered. What is the best way to write this formula?
-
Using column formula when the formula in row 1 is different to the other rows...
Hey guys, I'd be glad of some help.... I'm trying to create a column formula which uses a piece of data from the row above, which it doesn't seem to like, as it appears that the formula must be created in row 1 for it to work? I want to use column formula as I'm adding to the sheet via a form and want the column to auto…
-
Weird issue - COUNTIF Date Cross-sheet Reference
Hi - I'm doing a very simple cross-sheet reference for a count of dates and it's not working. Sheet 1: Date column has a series of serial dates. Column14 has a formula that is not working. Sheet 2: This has a Date Completed column. The formula in Sheet 1 in Column 14 is: =COUNTIF(Date:Date, {BPD-DateCompleted}) - ...where…
-
Cannot make a formula with a cell reference to another sheet work as expected.
I'm trying to count the number of Blocker (priority) tickets for a specific Business Unit. This is the formula that SHOULD work: =COUNTIFS({BU}, [Business Unit or Dept]:[Business Unit or Dept], "JM", {Priority}, [Priority]:[Priority], "Blocker", {Ticket}, [Jira Ticket #]:[Jira Ticket #], <>""). I have validated that the…
-
Logic type table help
Can anybody suggest a way to do this. I have it working in excel, I can't seem to get it working. I have this excel formula that works. =IF(AND(A2="OT", B2="UNDER"), "LOW", IF(AND(A2="OT", B2="OVER"), "MEDIUM", IF(AND(A2="LATE", B2="UNDER"), "MEDIUM", IF(AND(A2="LATE", B2="OVER"), "HIGH", "") ) ) ) I want to compare the…
-
Children Parent % not calculating
Hello, I'm trying to build a sheet that calculates a percentage off of a Status column, but the column is not returning a percentage and the =AVG(Children) section. This is my mess of a formula =IF(IsParent@row = 0, IF(OR([Rally Status]@row = "Submitted", [Rally Status]@row = "Acknowledged", [Rally Status]@row = "Not…
-
Auto-calculate a date but round to nearest weekday
I a creating a smartsheet that is calculating several milestone dates based on a single event date. This is the formula I'm currently using to calculate a milestone date 8 weeks prior to the event date, based on the event date being manually entered in the [Milestone Date]16 cell. Is there a way to make this return the…
-
Countifs multi select column and year column with contains
Hello! I have a multi-select column from which I am able to get a summary formula to count correctly, but every attempt I've made to account for the year from a second column has returned errors of some kind. The Year column has a formula in it as well: =YEAR([Contract Fully Executed Date]@row) This is the formula that…
-
Help stacking COUNTIFS?
I have two fully operational COUNTIFS functions that calculate independently without issue. One counts how many absences occurred in a specific time range by referencing a date column on an attendance tracking sheet. The other counts how many absences occurred in a specific location by referencing a location column on the…