# SUM a variable number of Rows between two points

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")
``````

## 1 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.

• 1. You might also want to get interested in using the INDEX/MATCH combo to help with the INDIRECT addressing 2. Your spreadsheet seems to be formatted like a document, ready to be printed. If you are interested in performing some more intricate calculations, it's better to maybe put the data in an unformatted but more regular, tabular format. This would enable you to use functions such as SUMIF to solve your particular problem. Commented Mar 28, 2017 at 14:18
• You're right that looks like it's formatted for printing. It is! This is edited by floor staff who have no real experience with spreadsheets, so it has to be as readable as possible. It's then printed and they each get their own copy when working outside. Having said that, do you have any suggestions for how I might format things differently in the future to make the calculations easier? Commented Mar 28, 2017 at 14:27
• You could make a column "Date" containing the (repeated) entries for Wednesday, then Thursday. Let's say this column would be the new column B. Then to sum what you have in column D for Wednesday only, you could use =SUMIF(D:D, B:B="Wednesday") Commented Mar 28, 2017 at 14:34