-
unexpected behavior with collect formula
I believe this formula should work, and when I break it apart it seems to work but doesn't when together. Copy from sheet =JOIN(COLLECT({Range 1}, {Range 2}, IF($[By Shift]$1 = "All", @cell = @cell, @cell = $[By Shift]$1), {Range 3}, IF(ISNUMBER($[Within Last (Days)]$1), NETDAYS(@cell, TODAY()) < $[Within Last (Days)]$1,…
-
formulating dates excluding weekends
I have 3 columns with different dates. My goal is to enter 1 date, and have the other 2 dates formulated based off the 1st date. I created a formula to satisfy this need, but I am ending up with some dates falling on weekends, which I cannot have. Is there a way to correct this issue without enabling dependencies and…
-
Formulas
I'm trying to move an excel project timeline to the Gaant template. I currently calculate my dates backwards from the desired in stock date. I've put in my duration for each step, but wanted to know how i can set aformula to deduct the duration days from the in stock date, rather than inputting a start and end date…
-
Countifs + find formula
I have a formula I'm having an issue with. I'm trying to count how many projects are assigned to a individual in the "Assigned To" column that contains a partial description "green Ties" in the "task Name" column. here's my formula I came up with and need help to get it right. =COUNTIFS([Assigned To]1:[Assigned To]160,…
-
Ignoring blanks in Countifs
I am trying to create a formula to look at multiple columns and count if they meet a specific criteria. I have a formula that works when there is data in the sheet but it causes an error whenever data is missing. I need to create a template that can be used to create several trackers so I need the formula to work when…
-
collect with conditional statement to return all values
I am attempting to use a collect with no criteria, that is it grabs everything and am having some problems. =JOIN(COLLECT(Return:Return, a:a, IF(ISBLANK(a10), true))) my desired behavior is that if cell A10 is blank, all of the values of Return (including where A is blank) are collected. This is part of a much larger…
-
Nested IF with COUNTIF
I need to use a nested IF with COUNTIF. Is that possible? The scenario is that I need to count the number of “Red,” “Yellow,” and “Green” balls that are in a particular row in another sheet. Then, * If count of “Red”>=1, return “Red” * If count of “Yellow”=1, return “Yellow” * If count of “Green”=8 (or all), return “Green”…
-
countif
Hello, I have a formula that I'm attempting to count the number of projects within the Task Name column that have "hot CX" as part of the name. Task name is customer- Hot CX My formula returns a 0 count. What changes do I need to make with my formula? =COUNTIF([Task Name]1:[Task Name]100, "Hot CX") Thanks!
-
Index(Collect add to formula page
Can we get an index(collect example added to the collect formula page? I have found it extremely useful in finding specific iterations of data, and I only stumbled upon it trying random things to solve a specific issue I had a while back. It could have saved me quite a bit of time if I had seen an example of it when…
-
Date Calculation
=IFERROR(NETWORKDAYS([NAI Project Start Date]7, [NAI Project End Date]7), 0) Cannot get this to calculate The first field has =Another_Cell but the property is set to date. Is this the problem? Im testing the date in the C1 field for being a date with NOPE being the return if its not a date
