-
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…
-
Trying to record time between changes in specific contact names
I am trying to record the response times of two individuals (A and B) when they receive notice of their name within a contact column (start time) and when they can remove their name from that column (end time). Note: there are dozens of other names in these sheets, but we are only focused on A and B. My thought is to…
-
Find the latest time entry and send a reminder if theres no entries in the future
Hi. I have built a form for our HR department to log their whereabouts for the following week. The team also receive an automated reminder every Wednesday to submit 5 entries (one row = 1 day) for each day of the following week. They simply advance the date field and select their whereabouts for each day. Although this is…
-
formula for past date
Is it possible to use the same formula but different length of time in the past? IF(AND([Quote]@row <> "Completed", [Date Sent]@row <= TODAY(5)), "Green", IF(AND([Quote]@row <> "Completed", [Date Sent]@row <= TODAY(10)), "Yellow", "Red")))) If today is 04.15.2024 and I need the symbol to change color as days past...…
-
The sum of values in the next row
Hello everyone, I'm having difficulty writing a formula and would like to ask for your help. If in the fourth column of the image below, a risk condition has been identified, I would like to sum the values below the row where the risk was identified, without including the row classified as a risk in a new column right…
-
Count is not blank
I am creating a sheet summary. I would like it to count only those in January and my column range cells are not blank. I feel like I am so close. =COUNTIFS([Audit Month 2]:[Audit Month 2], "January", NOT(ISBLANK([Test Issue 1]:[Test Issue 4])))
-
Cant figure out Summing 2 columns based on text in another
I'm trying to sum up two budget columns, based on the budget type in another column. For some reason I keep getting errors and can not figure this out. =SUMIF(Program:Program, "BDM", [PO Raised]:[Planned, not POd]) Program Column = Text/Number = "BDM" PO Raised & Planned, not POd columns are also text/number columns that…
-
Help with Formula
I'm working on a formula that will look at cell A if it is = "Milestone" perform this function, if not do this. Have that working. My issue is if the denominator is 0 is produces an error. I would like to add code to return 0 if denominator is 0. The top code is what I need to figure out. The bottom code is what is…
-
Help with COUNTIFS
Hello, I am trying to make a smart sheet in the same way as this example template. Form>collector sheet>metrics sheet> dashboard. Basically, I want the end result to be four different graphs tracking monthly results of yes, no, n/a, or other questions. I will include what I have so far and what differs after this example…
-
How would you most easily track that a date has changed to a new date that is 'x' different (+,-)?
We are using data shuttle to bring 'start date' in from another system (upon attachment for now; eventually, most recent when it sees it). If the start date changes we have users who want to know if it moved up 10 days, back 3, etc. I can create a hidden column to hold information and calculate - but I can't figure out how…
-
There has to be an easier way...
Hi smarties, I have 40 metric sheets (one per location). In each sheet I have an countif formula that references a data sheet. I have this formula in about 20 columns. A sample is in the screen shot. It says something like count if the name in the Frontline leader column of the metric sheet matches the name in the data…