-
Using SUMIFS with Cross Sheet References and CONTAINS
There seems to be lots of information on how to use SUMIFS with a CONTAINS criteria, referring to another sheet. Here is my formula on Sheet 1: =SUMIFS({Total Cost - Final Event Space Cost}, {Requested Event Date}, {Requested Event Date} > TODAY(), {Requested Event Date}, {Requested Event Date} >= TODAY(-365)) Total Cost -…
-
How to calculate sumifs across multiple columns
Hello community! I am trying to build a summary worksheet to get an overview of all the meeting categories but my raw data has multiple data columns and the formula I am using comes up as an error Summary sheet Raw data I need to get a total sum from both "no. of meetings chaired by CPO" and "no. of meeting attended as a…
-
Can I use the INDEX function multiple times in the same column on seperate rows?
Hello, I am trying to make a calculations sheet that will build a estimate based on input from another sheet. I have a helper reference row that actually grabs the input of a form on the origin sheet, and am using INDEX to reference the information from that row, and then multiply it by input on the same sheet. This worked…
-
Median and Mode options in Report Summary Options
We use Median for a lot of metrics as it helps to remove the outliers that can skew the data. Please add Median and Mode (if you are already updating it 😉) in the summary options for the reports.
-
Has anyone found a way to calculate CPI (based on planned spend and actual spend) with regards to to
I am using the following formulas for my calculations on the parent row in my project plan but need to find a way to have it based on today's date and not the roll up from the entire project SPI: =IF(AND([Planned Value]@row = 0, [% Complete]@row <> 0), [% Complete]@row + 1, IF([Planned Value]@row = 0, 0, [%…
-
Add months to a date
Hi Team, I need to create a formula to add X months to a date (X) to calculate a due date. Any suggestions? Thanks in advance!
-
I am getting #circularreference and I am not sure how to fix it.
I am trying to get this calculation to work =IF(TODAY() < [Start]@row, "not started", IF(AND(TODAY() >= [Start]@row, TODAY() <= [Finish Date]@row, [% Complete]@row < 100), "In progress - on track", IF(AND(TODAY() > [Finish Date]@row, [% Complete]@row < 100), "in progress - delayed", IF(AND(TODAY() > [Finish Date]@row, [%…
-
Formula to calculate the difference between two numbers based on a month in another column
I am unsure if it is possible to do what I want, but I wanted to ask. I have attached a copy of my grid. I want a formula to calculate the difference between the largest and smallest numbers for each well for each month. For example, for well #2 in month one, find the largest number (58650300) and subtract the smallest…
-
TIP: Calculations on an AutoNumber Column
I've noticed when trying to run formulas on my AutoNumber column, that it doesn't always work. Here's a table of some of the results. For each row I changed the autonumber "Numerical Places" from 0 to 00 to 000 to 0000. You'll see that the results conclude that Formulas on an Auto Number column MUST USE SINGLE DIGIT…
-
Mixed Sheet Structure
I have a Sheet where users enter tickets for Enhancements to current applications. These could be any type of requests and there are several steps necessary prior to approval to start work. There are two types of enhancements that when approved are 'tracked' on a different log, though specific fields updating the primary…