Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Summating multiple columns to a new column

Copper Contributor

Hi!

I'm an Excel-beginner and need to know how to add a new column and in this I want 5 other columns to be summated. 

This feels like it should be simple but I can't make it work. 

 

/Emelie

4 Replies

@emelie745 

I'm happy to help with your Excel beginner question! Adding a new column and summing values from other columns is indeed simple, and I'll guide you through it with step-by-step instructions and examples.

Here's how to add a new column and sum values from 5 other columns in Excel:

1. Insert the new column:

  • Click on the cell to the right of where you want the new column to appear. This will highlight the entire column on its left.
  • Right-click on the highlighted column header.
  • Select "Insert" from the context menu.
  • A new blank column will be inserted.

2. Sum the values from the other columns:

  • In the first cell of the new column (e.g., A1 if you inserted the column before B1), enter the formula:
Excel =SUM(B1:F1)
Use code with caution. (external link removed by moderator)
content_copy
  • Replace B1:F1 with the actual cell references of the five columns you want to sum. For example, if your data starts in row 2, use B2:F2 instead.
  • Press Enter.

3. Copy the formula down the column:

  • Click on the bottom right corner of the cell containing the formula (the small square). This will activate the fill handle.
  • Drag the fill handle down the column. The formula will be copied to all the cells below, automatically adjusting the cell references for each row.

Example:

Let's say you have data in columns B to F like this:

A B C D E F
1 10 20 30 40 50
drive_spreadsheetExport to Sheets

Following the steps above, you would enter the formula =SUM(B2:F2) in cell A2 and drag the fill handle down to A3 and A4. This will give you the following result:

A B C D E F
1 10 20 30 40 50
2 150        
3 150        
drive_spreadsheetExport to Sheets

As you can see, the new column A now contains the sum of the values in columns B to F for each row.

Tips:

  • You can format the new column (e.g., number format, alignment) by highlighting the cells and using the options in the Home tab.
  • You can change the formula to sum different columns by adjusting the cell references within the SUM function.
  • You can name the new column for easier reference by clicking on the column header and entering a name.

I hope this helps! Please let me know if you have any further questions or need help with specific scenarios in your Excel sheet.

@emelie745 

Can you provide more info?

Are your data in an ordinary range, or did you create a table (Insert tab of the ribbon > Table)?

Okay so i have 6 columns, FD, FE, FF, FG, FH, FI, and the values in each column is either 0 or 1.
I want that the next column; FJ adds the values from the 6 previous columns so that each row of FJ is the sum of the values on the same row (From FD to FI).
For example if on row 2, FD=1, FE=0, FF=0, FG=1, FH=1 and FI=1, I want the corresponding row in column FJ to add these numbers and show the number 4.
Since I have 9000+ rows in my dataset I need this to automatically apply to the following rows, so that each row of FJ sums the current row.
The first row is the name of each column/variable.

I hope this is enough info.

@emelie745 

Thanks! The easiest way to do this is to convert the range to a table, for formulas in a table will automatically be applied to all rows of that table, including new ones.

Start by entering a caption in FJ1, for example Sum, or Total.

With FJ1 still selected, activate the Insert tab of the ribbon, and click Table.

Make sure that "My table has headers" is ticked, then click OK.

Select FJ2 and type =SUM(

Then point to FD2 and holding down the mouse button, drag to FI2.

Alternatively, click on FD2, then Shift+click on FI2.

Finally, type ) and press Enter.

The formula should automatically be extended to all cells in column FJ in the table.