-
How do I get a formula cell to return to blank when the corresponding cells don't have any values?
I am using the formula below to calculate the number of years between today's date and a persons start date in another column. =(TODAY() - [Start Date at TDG]1) / 365 If the start date has not been entered, the Years @ Company cell is showing "0". How can I get the cell to show as blank until data is entered in the start…
-
VLOOKUP Multiple Matches
I have a table that sometimes has duplicate entries in a column. I'm doing a VLOOKUP on this column, however, obviously, if there is more than one match, this generates an error. What I'd like to do is return a match for the most recent duplicate entry, or in the case of a sheet that is filled via a form, the item farther…
-
Can't perform date calculations on a Date returned by a cross sheet JOIN(COLLECT(VLOOKUP function
Hi, To populate DueDate3, I'm using: =JOIN(COLLECT({GUT Column 7}, {GUT Column 2}, <>"Postponed", {GUT Column 2}, <>"Rescheduled", {GUT Column 1}, VLOOKUP(TaskName1, {GUT Column 1}, 1, false))) to return a date from another sheet. (It is a date data type in the other sheet). In another cell, I want to use something like…
-
How can I count the amount of actions in one specific month?
Hi, I'm trying to set up a counter for a number of actions in the month of may, for example if I have 5 actions this month (May), I want the counter to return "5". I've been trying to use the formula below but all I get in return is a zero: =COUNTIFS([Target Due Date]:[Target Due Date], IFERROR(MONTH([Target Due Date]1),…
-
Counting amount of actions in one month
Hi, I'm trying to set up a counter for a number of actions in the month of may, for example if I have 5 actions this month, I want the counter to return "5". I've been trying to use the formula below but all I get in return is a zero: =COUNTIFS([Target Due Date]:[Target Due Date], IFERROR(MONTH([Target Due Date]1), 0) = 5)
-
Count Formula to total percentage
I'm trying to get the overall total of percentage on all % of Project Done but it's working (77%) I'm using the count functionality as it's not working when I use AVG. Count formula I'm using. Overall total when i use microsoft spreadsheet is 144%: =COUNT([% of Project Done]3:[% of Project Done]122) / 145
-
Assign formula
Hello, I'm trying to get a count or sum of my team resources and having can't find the formula - help!
-
Adding a Second OR to Formula
Smartsheet Community: I have to add a second OR Statements to to the Formula but can not put my finger on what I am doing wrong. Current Formula: =COUNTIFS({UAT Issue Tracking Sheet Range 4}, "UAT - Phase 1", {UAT Issue Tracking Sheet Range 3}, "Highest", {UAT Issue Tracking Sheet Range 5}, OR(@cell = "New", @cell = "In…
-
Adding a value to a cell with pre-existing value based on another checkbox cell
I am trying to add a value to a cell if a checkbox is checked. The cell I am trying to add to is already populated with an existing value. I want to add the two for a new value. If Lift check box is checked then add $467.50 to revenue cell(already populated with $1800, for a total of $2267.50 Trying with this, but no luck…
-
Formula to reference one column based on entries from another column.
I need help on a formula for Project Assignment when a dollar amount is what determines who gets a Project assignment. For example, A Facilities coordinator will get a project if it falls between $5k - $24,999 Project Manager A will get a project if it falls between $25k and $99,999 Project Manager B will get a project if…