-
Creating a Sum for Filtered Rows
I have a Sheet which contains a complete list of all invoices, which Customer they are for, the Invoice Total and an Invoice Paid column which would show the balance of the Invoice. I have created a number of Filters for all of my Customers, so that I can filter straight to one Customer and see their outstanding balance.…
-
How to exclude values from a COUNTIF expression
I'm writing a COUNTIF formula referencing a column with multiple values that works well, until I try to build an expression that says: I want you to count the values in this column as long as they are NOT "x" OR "y" OR "z". Can anyone help with the proper syntax? I've tried the following but it doesn't work: COUNTIF…
-
SUMIFS Cross Sheet Issue
Hello Everyone! I am needing help with the SUMIFS formula for cross sheet reference but when I enter multiple criteria, it returns a value of 0. What I am trying to accomplish is a sum for Sheet-2 based on multiple criteria from Sheet-1: Criteria from Sheet-1 * Job ID* 216116 * Cost Code ID* 7 * 15 * 16 Sum Range: Act.…
-
Harvey Progress Ball at Parent Level
I have set up a column ("Task Complete Score") for numbering (0-4) to show progress which I am using to automate a column ("Progress") showing Harvey balls. I’d like to add a Harvey Ball for the parent row using an easy average. At the parent level, I have added this formula AVG(CHILDREN()) to get the average "Task…
-
Formula for RYG symbols with dates and checkbox
I'm trying to combine two formulas to automate RYG symbols. The first, this one, works fine to automate the symbols to be either red or yellow. Row #1 in the screenshot. The purpose is to keep the symbol red if the date column is either blank or empty, and yellow if there is a date in the column. =IF(ISBLANK([Start…
-
Excluding cells from COLLECT based on value
Hi All, I am trying to write a COLLECT which excludes results based on the value of a cell, similar to how NOT(ISBLANK(@cell)) works. Below is a formula I have made which works, but I was hoping there was a more succinct way to achieve the same result =JOIN(IF([Audio Language]153 <> "English", COLLECT([Audio…
-
IF FORMULA TO PUT AUTOMATIC STATUS
I would like to know what can I do to put automatic status for my monitoring sheet? There are 3 status: In Transit, Cancelled and Delivered. I want to know if its possible that when the requestor input the delivery date (refer to the screenshot), the status will change to "Delivered". For the Cancelled and Transit though,…
-
Most common text formula
IS there a formulas that collect the most common text in range of cells? I want a parent line to show the most common option in that column.
-
Copy contents of a cell into Comments
Is there any way to copy the contents of a cell into Comments? Or have comments on a specific cell? Or show the value of the last entry in Comments in a cell? Thanks.
-
How to create a future date based on a date in another column?
Hi, I am looking to create a formula in a column that automatically calculates the date of 12 weeks from the date of deposit column. Can someone please help with the formula? I keep getting errors. Specifically I would like the "estimated completion date" column to be the "date of deposit" column plus 84 days. Thanks! Keren