Google Sheets – How to SUM Variable Number of Rows Between Two Points

google sheets

How can I SUM a variable number of Rows, where the start and end points will be changing constantly? In the image below I would like to be able to add all the values for each choice together. However there may be rows added in as needed(As you can see Wednesday has more rows than Thursday).

If possible I'd also like to be able to show how many bookings there were for that day using a variation of the below formula:

=CONCATENATE(COUNTA(B2:B13), " Bookings")

Example Sheet

Problem Sheet

Best Answer

I think I've come up with something (Whenever I post a question I seem to instantly come up with my own solution!).

Using the following: 

=SUM(D$19:INDIRECT(ADDRESS(ROW()-1,COLUMN())))

I can add as many rows as I want between the start and end points and it will SUM correctly. The D$19 will update itself to whatever that cell becomes(D$20, D$21, D$22, etc). The INDIRECT(ADDRESS(ROW()-1,COLUMN())) will always get the cell above itself, so it will SUM everything above itself.

Is there a better way to do this? I know that the only problem I have right now is if a row is added before 10:00am it will not be counted in the formula.

Related Topic