-
Formula skipping rows
Hi all, I'm trying to use the below function to automatically change the color of Harvey balls to red, yellow, or green based on the length of time till a task's due date. =IF([Due Date]@row >= TODAY(+61), "Green", IF([Due Date]@row >= TODAY(=31), "Yellow", "Red")) However, for some reason some rows show "#invalid…
-
Math Formula Error
Hi, I am trying to create a formula to divide the Impressions column by 1,000 then multiply it by the Total Price column. This is the formula I am using "=VALUE(Impressions@row / 1000) * VALUE([Total Price]@row)". Not sure what's going wrong. Total price is a column formula that auto fills each cell with the $5.95 price…
-
How to count number of completed projects in a date range?
I am absolutely new to formulas and I'm struggling to find a way to count the number of completed projects during a 14-day range so I can share that information in graph form on a bi-weekly report. I have a tracker with fields for "status" and "actual completion date". I need to count the number of projects that have…
-
Percentage Formula
Hello, Sorry for the basic question, but I can't seem to get it to work, I'm looking to create a formula that will calculate 15% from the column [Product Price]. Thanks in advance
-
Index/Match where index is across 2 columns
Hi, I currently have a simple Index/Match to pull in the hourly rate from a source sheet when the freelancer's name matches. Some freelancers may work on a fixed rate instead. I would like to build a formula that indexes the fixed rate column in the source sheet, if the hourly rate column in the source sheet is blank. I'm…
-
SUMIFS question
On one of my sheets, I am summing a range based off of another columns date range and another columns value (true/false). Currently, the formula I am using is: =SUMIFS({JOBS AWARDED Lighting}, {JOBS AWARDED Bid Date}, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31)), {JOBS AWARDED Range 4}, @cell = 1) However,…
-
Automatically adding rows to sheet and automatically nesting them under parent row?
Is it possible to have a rows automatically added to a sheet (for instance from a form on another sheet), and have the rows automatically be nested under the correct parent row on the target sheet? For example, if the Primary column is "project name", and "parent project name" is a field on the form, is there any way for…
-
Count/sum only if one of children is a specific value and scope
Hello everyone, I was wondering if someone can help - I would like to do some financial departmental metrics in terms of revenue. Now, I have a sheet with about 300 different projects and would like to create another sheet where I will only see the sum of the revenue/totals for each of the 3 columns (as below). My costs…
-
Help with Counting for specific value within cells
I'm trying to get a count of the number of cells (within the column Card_StartDate) that have a date of less than 12/31/21, but only when an adjacent column (LaneTitle) has cells with content that contains 'Parent' (parent can be anywhere within those cells). I'm completely lost on this and can't find what I'm looing for…
-
Nested IF/AND or Something else??
Hi all, I am working on a formula to determine the Duration of a Build Phase so that I can use it to calculate the Status of a build. There are several criteria required to determine what Schedule to use. Once I have the Schedule I just need to know the Duration for the Phase it’s in. I’ve built nested IF/AND statements in…