1

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

2

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
    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. – ttarchala Mar 28 '17 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? – dlanmaar Mar 28 '17 at 14:27
  • 1
    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") – ttarchala Mar 28 '17 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.