-
SUM within a date range
Hi, I have 2 columns, 1 is a purchase amount, and the second is a purchase date. I'm looking to create a table from this that breaks down total amount purchased by quarter. I've tried the following among a few other variations with no luck: Q1: SUMIFS([Purchase]1:[Purchase]34, [Purchase Date]1:[Purchase Date]34,…
-
Index/Match/Match
I have two sheets that we are using for evaluations. The first is for a high level score and the other is for specific situations. The high level one will be filled out once per application and the specific situations will be evaluated separately per application. With this I would like to pull the high level scores into…
-
Count of Days Worked
What I need is a monthly count of what days were worked during the month (Based off form submissions) regardless of quantity. For example, if our Customer Repair/Renewal Workshop gets 10 submissions on December 1st and 15 submissions on December 6th, all I need to have the formula count is the 2 days worked during that…
-
Tracking a sum from separate column of specific values
Hello, First, I want to thank all of you for your help on this community. I'm learning a lot and this place has helped me out immensely. I've looked around and have found some similar questions, but I can't seem to get this to work, so I want to turn it over to y'all. So let me start with a snapshot of my sheet. What I…
-
% Complete based on Todays date, Start and End dates
I'm looking to return a % complete based on the start and end dates and the current date. So if a task starts on the 17/7 and ends on the 31/7, and today is the 24/7 - the % complete would be 53%. - (I want to have this as a separate column so I can keep dependencies on the actual % complete column) =(MIN(TODAY(),*finish…
-
How to make a calculation return blank
I have a simple IF() calc that I thought would return blank if false, but when I go to make a color rule on if this column is blank it doesn't work. If I copy the output of this calculation is appears to be returning whitespace, but I can't use [field] is equal to {put a space in the text box} in the color rule.…
-
FORMULA
If we already completed the row task and it's marked complete, it will automatically change "Balance to Bill row" to 0. If not completed I want the cell to have the formula of Project amount minus QTY Billed so they can get the pending balance. Keeps coming up Unparseable. See attachment. Thank you.
-
Summary Field, Date Within Range using SUMIFS
Hi, I am trying to use the following SUMIFS formula but I receive a "#INVALID OPERATION" error when trying to create a Sheet Summary Field using the formula. =SUMIFS(Hours:Hours, Status:Status, "Confirmed", FinishDate:FinishDate, >=12/10/2021) However, if I remove the ">=" and just use the date 12/10/2021 I do not get the…
-
formular syntax code editor
Hi all, I have some complex and long formulas in my sheet wit a lot of brackets. Do you know any syntax editor which I can use to document and analyse the formulas? Should have highlighting for opening and closing brackets etc. Thanks
-
IF Function to return Yes or No depending on difference between two dates.
Hello, I have two date columns; [First Date of Absence] and [Start Date]. I am trying to write a formula that returns "YES" if the difference between [First Date of Absence] and [Start Date] is less than or equal to 182 and returns "NO" if the difference between [First Date of Absence] and [Start Date] is more than than…