-
Counting total based on Status & Date
Hi, I'm trying to calculate the total value of all the won job in this financial year. I have to reference 3 columns for this (Date submitted, Value, Status) not sure how to make the formula. This is as far as I got. Please help. =SUMIFS([Tender Due]:[Tender Due], >DATE(2018, 6, 30), <DATE(2018, 6, 30), Status:Status,…
-
Symbols Formula. How do I make a symbol depend on two other columns?
Hello! I am trying to make a symbol formula that will become "1" (red flag) IF the Start date is in the past, AND if a dropdown list DONT show "Done". Anyone that has this formula or knows how to write one? Kind Regards// Hugo Martinwall
-
VLOOKUP return the second value that is finds in the table
Hi, I am looking for a formula that can return the first, second, third ... value in the table. Using Vlookup will only return the first match Any suggestions!! Thanks Fadi
-
Date Calculation Question
Can anyone share with me on how I can calculate what I call the 'Next Quarter Projects'? For example: I have a start date and end date columns for each project, and I want to create a Filter/View that shows me the projects that are due in between a date range(ie Q2, Q3, Q4, etc) Can anyone help? Thanks!
-
Help with cross sheet formulas
I have a property list setup that contains floor plans and costs for those floors. In another sheet i have a column setup as "bill rate." In the bill rate column, i would like to write a formula that would go to the property list sheet, search the property name column, match the floor plan and cost of the floor plan The…
-
Calculate a conversion rate on a specific date
I have a budget that has expenses in both USD and EUR. I report expenses in USD to our finance department. I'd like to be able to calculate the euro to usd conversion rate for the date that something is paid. Is there a formula not to pull the current conversion rate, but a conversion rate from a specific date, in this…
-
Pull in average of column for certain rows in sheet
I have a tracker setup that I track support requests. I have a few metrics that allow me to see AVE SLA time to completion and assignment. The problem I am having is I want to show this same AVE SLa time value BUT only for specific rows based on a value in one of my columns. I seem to have a mental block on how I can…
-
How to have child rows displayed using parent data
Hi all, I have a complex sheet which is efectively a "to do" list. Thanks to the community I have it working pretty well as I want and am very happy with it. To polish the sheet, I would like to achieve the following: I have my todo's organised by project then category. For example, I have a column called "project" which…
-
Column Average
Hello! I am trying to calculate the average of a column. It seems like this should be a very simple task. I'm typing =AVG then referencing the column I need to average from another sheet. It generates the following formula =AVG({Employee Reviews - PFP Range 7}) However, when I hit enter, I get a #DIVIDE BY ZERO error. Any…
-
Counting and applying "color status" for parent/children rows
I have overall Annual goals (purple lines) with sub projects that fall into a particular category. I then have subtasks that fall under the corresponding item (identified with an alphanumeric task number). I want to be able to summarize (on a dashboard) a couple of things: the task health of the project level (task 25 with…