Numbers - Sum if column/row added

[NStocks]

Hi,

I'm creating a relatively simple spreadsheet where the sum of a row is added at the end of that row. I want to be able to add up the total of x amount of rows, with the ability to automatically include any additional rows without rewriting the formula.

Is this possible?

 

[mmkerc]

Not sure if I follow what you want as the description does not align with the screenshot.

If what you want is adding rows together with a sum total at the end of each row, and then a total of all the rows

a. At the end of each row use the sum function to total the row. Assume data entered in row 1 column a-c the formula in column D is: =sum(A1:C1)
b. At the bottom of the total column add the sum function to total the column. Assume there in data in rows 1-3, the formula to add in cell D4 is: =sum(d1:d3)
c. Right click row 3 and add row below. This will copy add a row between row 3&4 with the row total formula, and also included it in the column total formula which is now in row 5.

If you want add a row to your screenshot right click on the bottom row (with the 15 hours shown) add row below. Assuming the total formula is adding the two rows above (w/ the values 2160;7200) is the sum function at shown in (b.) above the total row will be moved down one row with the formula intact.

 

[NStocks]

I've made a quick video below to try and explain it better.

The £ value totals are a two-part formula. First it's calculating the hours of all the month, then it multiplies the total hours by the £hr rate. This all works fine.

When I add up the values of these two rows, I get the sum. This works fine. The part I'd like to develop is if I add another row, I want the total of all three rows (or however many rows I add) to automatically update the total at the bottom. The video shows if I add a row, it doesn't update the total.

Appreciate your help.

 

[mmkerc]

I could not get your video to work (issue on my end). However if you go the the row number (left most column in your spreadsheet) of your last data row. Right click you should get a contextual menu with the option to add a row below. That will add a new row, without data but with the formulas intact (formula for summing hours x rate). If the total formula for the sum of all the rows is written per my example (using the sum formula) that should work.

 

[HobeSoundDarryl]

OP, the easiest way to get what you want is to build a blank row into your spreadsheet and include it in the sum calculations. Then when you want to add more rows, select blank row, then "add row above (shifting blank-but-included row down). This will then include the newly created row in the existing formulas.

Describing the same in row names...

• Rows A & B have data you want to sum in them.
• Include a blank Row C
• Make your "sum" formulas sum up A+B+C
• Now you want to add a new row, which seems like it should be D. DO NOT add a row below C. Instead, insert a new row above C or below B, so that what was C becomes the new D row.
• The formulas will now be summing up rows A-D. Enter your new row data entries in the new C and they will work.

The same works with sum formulas across the row. Insert a final blank column and include it in the SUM formulas. Then when you want to add a new column to also be included in the sums "Add column before" (the blank one) or "Add column after" the last one with data. In your video example, that would be (a new) blank column AF (to then be included in the calculations under "Monthly Total"). At some point, you wish to add a new column, so you "add column before AF" or "add column after AE" and the sum functions to the far right of columns will auto update to include the new column you just inserted.

If you are worried about aesthetics of a blank row or blank column, set this up to get the formulas working and then select the blank row or column and choose "hide row" or "hide column." They will still be there but now hidden.

There are other ways to do this too but this is the simplest- best I know.

 

[NStocks]

Thanks for the replies. It was as simple as adding a blank row and making the sum A+B+C. Now when I add a new row below B, it updates the total

 

[ivanwi11iams]

OP, the easiest way to get what you want is to build a blank row into your spreadsheet and include it in the sum calculations. Then when you want to add more rows, select blank row, then "add row above (shifting blank-but-included row down). This will then include the newly created row in the existing formulas.

Describing the same in row names...

• Rows A & B have data you want to sum in them.
• Include a blank Row C
• Make your "sum" formulas sum up A+B+C
• Now you want to add a new row, which seems like it should be D. DO NOT add a row below C. Instead, insert a new row above C or below B, so that what was C becomes the new D row.
• The formulas will now be summing up rows A-D. Enter your new row data entries in the new C and they will work.

The same works with sum formulas across the row. Insert a final blank column and include it in the SUM formulas. Then when you want to add a new column to also be included in the sums "Add column before" (the blank one) or "Add column after" the last one with data. In your video example, that would be (a new) blank column AF (to then be included in the calculations under "Monthly Total"). At some point, you wish to add a new column, so you "add column before AF" or "add column after AE" and the sum functions to the far right of columns will auto update to include the new column you just inserted.

If you are worried about aesthetics of a blank row or blank column, set this up to get the formulas working and then select the blank row or column and choose "hide row" or "hide column." They will still be there but now hidden.

There are other ways to do this too but this is the simplest- best I know.

Since I switched from Windows to macOS, this is what I do now.
However, I was used to Excel on Windows simply processing the SUM, based on all the rows in-between, even added rows.

 

[GerritV]

Instead of having your formula saying "=AG4+AG5", try entering =SUM(AG4:AG5).
If you now insert a row, the formula should update to =SUM(AG4:AG6).

To make it even more secure, I would first add a 7th row, and enter the formula as =SUM(AG4:AG7).
In this particular case, use the 7th row to click and add yet another row above. The formula should now read =SUM(AG4:AG8). Enter your additional data in the newly created 7th row, and repeat the process.

HTH

 

[mmkerc]

Thanks for the replies. It was as simple as adding a blank row and making the sum A+B+C. Now when I add a new row below B, it updates the total

One note of caution. Make sure you select a row with the formula you want duplicated in it. If you selected the "total" row in your original screenshot (w/ 9360) and choose add row above it will not work. You need to select the last row that totals the hours x rate (7200) and choose add row below.

 

[NStocks]

I'm looking to develop this further to give me monthly totals for invoicing.

It seems like it could be quite complex, however.

Looking at Month 1, if I followed that downwards to the monthly total row, I would need to multiple each month (hrs) but he charge our rate (£) for every month and every person. I would need to do this for all rows to total all works/hours for each month (For RIBA stage 5 and 6 in this case).

Is there a formula that would work for this, again following the same principal of allowing rows/cloumns to be added/deleted?

Link to .numbers https://we.tl/t-j0D9ozpx9X

 

[GerritV]

I'm looking to develop this further to give me monthly totals for invoicing.

It seems like it could be quite complex, however.

Looking at Month 1, if I followed that downwards to the monthly total row, I would need to multiple each month (hrs) but he charge our rate (£) for every month and every person. I would need to do this for all rows to total all works/hours for each month (For RIBA stage 5 and 6 in this case).

Is there a formula that would work for this, again following the same principal of allowing rows/cloumns to be added/deleted?

Link to .numbers https://we.tl/t-j0D9ozpx9X

I'm trying to understand your question. Can you perhaps rephrase ?

 

[NStocks]

In each month, there is a staff member with £/hr rate and how many hours per month they will be working.

What I want to do it calculate the total per month that will be invoiced. This means each staff members hours will be multiples by their rate. For example in Month 1, we have Director at £90.hr for 50 hours. The row below is Associate Director with the same £rate and hours.

Adding all the cells vertically for month 1 etc. will provide the monthly total for all staff for that particular month.

The formula would be hrs * rate per row, which totals at the bottom, currently shown by 'XX'

I'm wondering if there is a formula that would allow me to add and remove months and staff members, whilst still retaining the formula provide the monthly invoice amount.

 

[GerritV]

Thanks for the additional explanation.
Quick and dirty would be to add a column for each month, and total that one at the bottom.
For now, here's a screenshot of what I mean.
You think you can live with that, or do you want a more streamlined (automated?) method?
I'm thinking pivot tables, perhaps.

 

[NStocks]

Thanks for your reply.

I thought about doing this, but adding 12 or more additional columns makes the spreadsheet very wide and I'm limited by paper size. Perhaps I could still do this and hide the columns with the total?

I'd appreciate your thoughts on a more streamlined approach (without having extra columns)

 

[mmkerc]

Without trying to overthink the issue, make one worksheet that works for one month, then duplicate the worksheet 12x. Your end solution will be a worksheet for each month. The formulas and processes advise before will work.

If the company is large (# of employees) you can create a lookup table to the changing variables (rate).

 

[Septercius]

I think the "SUMPRODUCT" function would work for what you want.

Let's say the "Charge out rate" is in column D and month 1 is in column E. Let's also say you have data in rows 5 to 10.

Your formula for the total for month 1 would be:

=SUMPRODUCT(D5:D10, E5:E10)

This should provide the required total.

You'd need to adjust that slightly as if you just copy it for months 2 to 12, the references would change. So:

=SUMPRODUCT($D$5:$D$10, E5:E10)

This will force it to use the data in the charge out rate column for the first range.

You then need to change this once more to accommodate you adding and removing rows from the spreadsheet. The "INDIRECT" function would do this, and would also help with your original question.

You could use this with "SUMPRODUCT", together with the "ROW" and "COLUMN" functions. For example:

=SUMPRODUCT(INDIRECT("r5c4:r" & ROW()−1 & "C4",FALSE), INDIRECT("r5c" & COLUMN() & ":r" & ROW()−1 & "C" & COLUMN(),FALSE))

Here, "INDIRECT" is using what's known as "RC" cell references. For example, "r5c4" is row 5, column 4, i.e. cell D5. You'll need to update these numbers for your own spreadsheet.

 

[NStocks]

I think the "SUMPRODUCT" function would work for what you want.

Let's say the "Charge out rate" is in column D and month 1 is in column E. Let's also say you have data in rows 5 to 10.

Your formula for the total for month 1 would be:

=SUMPRODUCT(D5:D10, E5:E10)

This should provide the required total.

You'd need to adjust that slightly as if you just copy it for months 2 to 12, the references would change. So:

=SUMPRODUCT($D$5:$D$10, E5:E10)

This will force it to use the data in the charge out rate column for the first range.

You then need to change this once more to accommodate you adding and removing rows from the spreadsheet. The "INDIRECT" function would do this, and would also help with your original question.

You could use this with "SUMPRODUCT", together with the "ROW" and "COLUMN" functions. For example:

=SUMPRODUCT(INDIRECT("r5c4:r" & ROW()−1 & "C4",FALSE), INDIRECT("r5c" & COLUMN() & ":r" & ROW()−1 & "C" & COLUMN(),FALSE))

Here, "INDIRECT" is using what's known as "RC" cell references. For example, "r5c4" is row 5, column 4, i.e. cell D5. You'll need to update these numbers for your own spreadsheet.

Thank you so much. It works like magic!

Is there a way to rotate the text in the monthly total, so that the £value is vertical instead of horizontal? (to keep the cell width narrow)

 

[GerritV]

Whenever I make a spreadsheet type report, I try to separate the data input from the results.
That's what I meant earlier by "streamlined" method.
Perhaps that's none of your concerns (right now), in which case I apologize for my suggestions below.
But I would like to show you what's possible - eventually.

1. Instead of typing the staff role, I defined the cells in column A to take predefined values from a list
2. Same for the rate in column B
3. 2 columns per month, but you can hide or minimize the second one in each month
4. Created a pivot table to show results

I'm attaching a few screenshots for you to check out.
Let me know if you'd be interested in the .numbers file.

The downside of using the pivoting table, IMHO, is that you have to recalculate it after each change in the Data table.
See the arrow in the second image, showing the Recalculate Button.
One advantage is, like I said in the beginning, that you can separate data from results.
At least one other example is that you can present the results in many ways, using the Organize panel at the right side of the window.

Note: you can insert the Pivoting table(s) on another worksheet to facilitate printing.

Sorry for the Dutch / Euro...

 

[NStocks]

Thanks for sharing, I'm learning a lot from the different ways to go about this and I appreciate the suggestions!

If you're happy to, I'm be interested in seeing the .numbers file as the dropdown values look like a great interface to use

 

[GerritV]

Here you go

 

[NStocks]

Thank you so much. It works like magic!

Is there a way to rotate the text in the monthly total, so that the £value is vertical instead of horizontal? (to keep the cell width narrow)

Has anyone found a way to do this? Is it possible?

 

[GerritV]

Has anyone found a way to do this? Is it possible?

I don't think that can be done in Numbers.
You can narrow the spacing, but that will eventually affect readability.