I am trying to count the number of dates submitted from an entry provided via form. I have figured out the formulas to do that. The problem is that every time a new form entry gets submitted a new row gets created and I have no way besides manually dragging down each time the formulas to include the newly arrived entry.
Is there any way to do this automatically? I have read out there that you can do this with an arrayformula with a range of the whole spreadsheet, but I haven't been able to make that work, since each cell would have to count the dates on their own row (and this range would change in each cell)
Here's the sample sheet https://docs.google.com/spreadsheets/d/1WkaIq5cNpyqvqJ6UdUQHd4jsaPlDDl9rocKZRses3DA/edit?usp=sharing
My question: How can I do step 1 automatically every time a form entry gets submitted? Is there any way of doing this just with formulas (if so, how)?
PS: I have also tried copying the form responses to a different sheet and calculate from there, but every time a new row gets created the formulas adapt to point at a row below the new one created. E.g. if the formula points to B6 and the new row gets created in row 6, the formula suddenly points to B7.