Excel SUM: Formula for column from midway to rolling end point

Question

I am creating a spreadsheet that shows the shifts I do at work. Column A represents the date of the shift, and then B-F represents the different shift role. I put =1 in the correct B-F column, and then have a separate table to show how many shifts I did of each role.

I am working out what percentage each role is to my total number of shifts. So far, so easy, as that secondary table just needs full column formula to work. Current formula for that total: =SUM(B)÷(ROWS(A)−1). Dividing the total value of B column (1 = I did that shift) by the number of rows in Col A (which is all my shifts that I worked, -1 for the header) gives me the correct percentage of total shifts that were that role.

However, I now want to add a third table that shows the percentages of different roles after a certain date. As I keep adding new dates to the table, an =SUM(B85:B108)÷(ROWS(A85:A108)−1)formula would keep needing to be added to.

Is there a formula to allow me to automatically expand the bottom end of my search parameter every time a row is added?

Answer 1

Use a table format by highlighting your data and hitting CTRL + T this will make the references named ranges when you highlight the data while you write your formulas.

Then as you add data to the bottom the range will expand and all referencing will automatically adjust if it's setup properly.

As an added benefit, this will make the formulas run much quicker, since they will be referencing the minimum amount of information required, rather than every cell in an entire column.