-
Countif, Collect, index
I'm trying to count all installations for a site from the master grid sheet to a new grid sheet. The formula I created only brings back two records for each site. =COUNT(INDEX(COLLECT({**Master Grid "installed" column** - Store Install Schedule Range 7}, { **Master Grid "Store#" column** - Store Install Schedule Range 5},…
-
Sum if unique names only
Hi there, I have a rollup sheet that adds the amounts if the names are unique. I get the names with a vlookup formula to get them from the source file. The source file has the names entered multiple times but their amounts are the same. I identify unique names with a formula that places the #1 in the column CountUniques.…
-
Formula with range that goes from a certain row to whatever the current bottom row is
My formula: =AVG(COLLECT(IIQ28:IIQ99, [SE Manager]28:[SE Manager]34, "John Lennon")) First, I cannot use the entire column. There is data in rows 1-27 that can not be applied to my average. I want the range to go all the way to the bottom of the sheet, because this sheet is regularly populated by a workflow from another…
-
Continuously evolving Sum formula
Is it possible to create a formula or workflow that would move/update our "90 Day total" column as new months come and go? I've attached what we are currently working with. The 90 Day Total is just a sum formula for each Jun Jul and Aug. The rest of the year is mapped in the exact same style, but for Sep Oct Nov and Dec.…
-
Return unique values while ignoring blanks.
Hello! I'm trying to find a function similar to vlookup or index that will return a unique value where there are duplicate search values. In this example, individuals book a desk on a given date. Since form submissions create a new row, the bookings flow in like this: Sheet # 1 My goal is now to collect these bookings on a…
-
I want to return a negative number as this sheet will be used for our accounts payable team.
I'm using a simple Sum formula. I want to display this as a negative. conditional formatting to change the color will not work. It needs to either be -$192,000 or (192,000) =SUM([2021 Total]1:[2021 Total]46) = 192,000 I've tried sumIF with no luck too. it's really just adding up all the values but I need the total to be…
-
Formula for replacing specific characters
Hi everyone! I am struggling with the formula language for this. So in Column 1 I have the file names with their file extensions. In column 2 I'd like a list of the filenames WITHOUT the extensions (see the top row) Is there a way to write a formula to replace the LAST 3 characters with nothing? Thank you!
-
Formula for At Risk Flag
Hi Experts, I am using the following formula to turn the At Risk flag red when the condition is true. But I am getting a #UNPARSEABLE error. The formula is trying to say: If either of these conditions is true, I want to turn the At Risk flag red: When an End Date is within 3 days of today AND % complete is below 80% When…
-
Formula issue
Hi Can someone help me to write these five formula in a single formula to get the schedule health. IF(Status@row = "Not Started", "Gray") IF(Status@row = "On Hold", "Yellow") IF(Status@row = "Not Applicable", "") IF(Act1@row >Act2@row , "Red") IF(Act1@row <Act2@row , "Green") THanks
-
Formula not working
Hi , Can anyone help me with this formula issue:- I have 4 symbol color- Not Started- Gray Not Applicable- No color On Hold -Yellow Complete- Green Act1 greater than Act2 and Status is equal to In Progress - Red else Green =IF( Status@row <> "Not Applicable", IF( Status@row = "Not Started", "Gray", IF(Status@row = "On…