-
Help on formula: Return value
Hi all, I am building a project tracker however I am new to creating formulas so please need your help again. Based on the image below, I wanted to get the Stage and the Progress of the project that will later complete my summary report by just displaying the main headers in yellow. On row 1, Stage column, I want to get…
-
SumIfs Current Month
Hello, I am trying to sum hours noted in the children of a row (Total Hours per Project) if the current month is noted in another cell (Week Ending With). Here is what I have: =SUMIFS(CHILDREN([Total Hours per Project]1, [Week Ending With]:[Week Ending With], MONTH(@cell) = MONTH(TODAY())) and I continue to receive a…
-
How can I summarize people working by hour on a simpe excel sheet?
This might be simple for one of you but it's driving me crazy. Please help. The enclosed pic is part of a simple employee schedule where employee daily hours are entered on a "time in" and "time out" basis. Below the schedule you'll see a breakdown by hours of how many employees are needed for that day (which is entered…
-
Using Countif and And together?
I am looking for some help on this one. I need to be able to find duplicates but only under specific conditions. For example, I have a list of SKUs as one column, and a second of the order status of that item. I need to know when multiple of the same SKU are in the same step of the process. So, instead of…
-
Help with nested If formulat
These formulas work independently. I am attempting to combine them into one nested if formula. 1) if the %complete is full, status is green: =IF([% Complete]10 = "Full", "Green" , "Red") 2) if the finish date is greater thant today, status is green: =IF(Finish10 > TODAY(), "Green") 3) if %complete is full and finish date…
-
VLOOKUP and Conditional Formatting
I updated our smartsheet team schedule to grab dates from my department's master schedule. However, the conditional formatting we set up that visually shows us when a project is nearing or past its due date no longer works on the VLOOKUP cells Is there something I'm doing wrong? Or is this a limitation of the software? Has…
-
% as Negative
How do you structure your formula calculate negative if it is over 100% For example, I am at 144% which is over budget Current Formula =AC1 / [Project Budget]1 Results 144%, I would like it to calculate -44% once I am over budget.
-
Nesting SUBSTITUTE functions
Hello All, I need help!!! I am trying to write a formula that replaces the letter "a" with the number "1", "b" with "2", "c" with "3", etc.... The individual formula of (ex.) =SUBSTITUTE([Column3]1, "a", "1") is easy enough, but for whatever reason I can't seem to figure out how to nest it with the rest of the letters and…
-
Counting Unique Values based on a date criteria
I am using this formula to count for unique values within a range of cell. I want to be able to use a criteria, a date for the range of cell, what should the formula look like? =SUM((IF(FREQUENCY(MATCH(B2:B16,B2:B16,0),MATCH(B2:B16,B2:B16,0))>0,1)))
-
Help on formula: How to copy adjacent text using a specific text from a different column
Hi all, Please need help on formula. The example image below is a project with sub tasks and I wanted to create a formula in the Progress column highlighted in yellow (this is the main header). The condition is: if the status of the row is "Active", then show me what is its current Progress. Based on the screenshot below,…
