-
Sorting and conserving formula
When I sort rows, the formulas are not maintained: = SUMIF($[column_A]$7:$[column_A]237, "Received", [Column_B]$7:[Column_B]237)) gets transformed into this: = SUMIF($[column_A]$57:$[column_A]237, "Received", [Column_B]$57:[Column_B]237)) the $ has no effect. and I cannot use the [column_A]:[column_A] syntax as it will…
-
COLLECT – IF – DISTINCT Challenge
I want to JOIN(COLLECT) unique, distinct instances of a date given values in other columns. I have a history log of participant registration dates. The participant can register more than one time on a given date. I want to JOIN(COLLECT) all of the unique registration dates an individual registers. Is this possible? Table 1…
-
How do I add another IF statement to a Workdays calculation?
I am using the following formula (from a template) top calculate Working Days Remaining for a task - =IF(ISERROR(NETWORKDAYS(TODAY(), [End Date]@row)), "", NETWORKDAYS(TODAY(), [End Date]@row)) However, I want to add a condition to this that if the Status column for that same row is marked complete, then the Working Days…
-
Sumifs Date Range w Cross sheet formulas
Im close on a sumifs formula, but for some reason i cant quite get it. trying to count installations on a give date from a given list of properties Here's what i have. it's returning a unparceable response =SUMIFS({7900 Park Central Install QTY}:{7900 Park Central Install QTY}, {7900 Park Central Install Date}:{7900 Park…
-
Help with Vlookup + date
I explain, I need to create an expiration date from a drop-down menu where the different types of payments of my clients are informed. Example, if a customer's account is "up to date" it means that the due date will be the issue date + 0. Also if another customer has a "credit" deferred payment type, the due date must be…
-
Sum of Children - not working
Hello All, I'm trying to get the sum of children. There are 4 children rows, with multiple rows beneath that. See image. In this case, the top row with cell value 0 would have a 2 because it's summing the purple 0 and purple 2. Purple 0 and Purple 2 are counting the cells below (children) that have checkboxes. I'm using…
-
Using CHILDREN(cell address) as a range in SUMIFS
I am trying to find the total of Task Total Hours only for rows with a tier level of 2 and where % complete = 1. I am writing this in a summary cell in a mini-dashboard that requires me to write the formula in the % Complete column. I am trying to use CHILDREN(cell address) as a range. What I have read leads me to believe…
-
Help! Already reached Vlookups Max reference Cell count of 25,000
I need a solution to the vlookup max reference cell count of 25,000. I have a database that zapier loads data too from an application. The database consists of 134 columns, and will grow to an estimated 300+ row count. Which works out to 39900 cells, which is 14,900 cells over the limit. I use a summary sheet to gather…
-
Adding Checkboxes
I am trying to created check point progress with check boxes and a visual aid. I created a If formula, but my progress is not adding up. I would like to know if anyone can help me with a better formula.
-
Autopopulating contacts using vlookup
Hi, I have a sheet with several formulas, including a =vlookup on a text cell and =Join (). All these formulae autopopulate when a new row is added manually of via a form submission. However, I also have a contact column which, while successfully pulling the contact information into the cell using a =vlookup function, is…