The following sample Table contains students’s marks.

__Method-1 –__Using a Structured Reference as an Excel Table Reference

*Marks1* is the sample table.

** Steps**:

âž¤ Select

**G5**and enter the formula.

`=SUM(C5:C7)`

**C5:C7 **is the range of the marks for *Daniel *and **SUM **Â adds these values.

By selecting the range **C5:C7**, Excel will convert them automatically to the structured reference system and modify the formula:

`=SUM(Marks1[@[Physics]:[Biology]])`

*Marks1* is the name of the Table, **[Physics]:[Biology] **is the range of the contiguous 4 columns; *Physics*, *Chemistry*, *Math*, and *Biology*. **@ **is the current row.

Press **ENTER**, and the added marks for all rows will automatically be displayed in* Total*.

__Method 2 – __Using the Absolute Reference System as an Excel Table Reference

In *Marks2*Â the **SUMIF function **will sum the marks of *Physics*, *Chemistry*, *Math, *and *Biology *for Jenny.

** Steps**:

Enter the following formula in the

**C14.**

`=SUMIF(Marks2[[Student]:[Student]],$B$14,Marks2[Physics])`

**Marks2[[Student]:[Student]] **is the range: *Marks2* is the Table name and **[Student]:[Student] **is the absolute referencing in the Student column; **$B$14 **is the criteria, and **Marks2[Physics] **is the sum range: the *Physics* column.

Press **ENTER **and drag the **Fill Handle **tool to the right.

It will copy the formula to cell **D14 **and showcase the following formula

`=SUMIF(Marks2[[Student]:[Student]],$B$14,Marks2[Chemistry])`

The absolute referenced data range is not changed, onlyÂ **Marks2[Physics]** changed to **Marks2[Chemistry] **to return theÂ *Chemistry*.

Copy the formula for *Math *and *Biology *to see the marks for *Jenny*.

__Method 3- __Using the Relative Reference System as an Excel Table Reference

In the table *Marks3,* marks in *Physics* and *Chemistry*, *Chemistry* and *Math*, *Math *and *Biology *will be summed for the students in the **Phy+chem**, **Chem+Math**, and **Math+Bio **columns.

** Steps**:

Enter the following formula in

**G5Â**

`=SUM([Marks4[@Physics]:[Marks4[@Chemistry])`

*Marks4* is the Table name, **[@Physics] **and **[@Chemistry] **is the corresponding row in the *Physics* and *Chemistry* column.

The relative reference system is **Marks4[@Physics]:[Marks4[@Chemistry]**

Press **ENTER **to see the sum of marks in the *Physics* and *Chemistry* columns. Excel will change the formula into:

`=SUM([@Physics]:[@Chemistry])`

Select the cells in the **Phy+chem **column and drag the **Fill Handle **tool to the right.

In the **Chem+Math **column, the formula is the following:

`=SUM([@Chemistry]:[@Math])`

It will sum the marks of *Chemistry *and *Math *for each student.

The sum of *Math *and *Biology *marks is displayed in the **Math+Bio **column.

`=SUM([@Math]:[@Biology])`

__Method 4__– Referencing Multiple Non-Contiguous Columns Using an Excel Table Reference

Here,* Marks5* is the Table name. Marks in *Physics *and *Biology *will be added.

** Steps**:

Enter the following formula in the

**C13Â**

`=SUM(Marks5[Physics],Marks5[Biology])`

*Marks5* is the Table name and **[Physics]**, **[Biology] **areÂ the *Physics* and *Biology* columns.

Press **ENTER**Â to see the sum of the marks in *Physics *and *Biology *for all students.

__Method 5 – __Referencing the Intersection of Columns Using an Excel Table Reference

Here, *Marks6* is the Table name. Marks in *Chemistry *will be added for all students.

** Steps**:

Enter the following formula in the

**C13.Â**

`=SUM(Marks6[[Physics]:[Chemistry]] Marks6[[Chemistry]:[Math]])`

*Marks6* is the Table name and **[Physics]:[Chemistry] **and** [Chemistry]:[Math] **are the two ranges. The intersected column is **[Chemistry] **which will beÂ referenced.

Press **ENTER to see** the sum of marks in *Chemistry *for all students.

**Similar Readings**

__Method 6 – __Using the Total Row Option for Filtered Tables

In *Marks7*Â the **Total Row **option will be used to sum the marks.

** Steps**:

Select any cell in the Table.

Go to the

**Table Design**Tab >>

**Table Style Options**Â >> click

**Total Row**.

A new row (**Total**) will be added.

âž¤ Click **C12 **and click the dropdown sign.

Select **Sum**.

You will see the sum of the marks in *Physics* and the following formula will be displayed.

`=SUBTOTAL(109,[Physics])`

**SUBTOTAL **will add up the visible values of the filtered table, 109 is for **SUM**, and **[Physics] **is the column name.

Hide the marks of a student (Jenny, here) by unclicking her name and pressing **OK**.

The total marks in *Physics *are changed to 542 as **SUBTOTAL **only works for visible cells.

Unhide Jenny and see the sum of the marks in *Chemistry, Math, and Biology.*

Adding new data (Leonardâ€™s marks) will update Total values.

Deleting Leonardâ€™s data, returns the previous results.

__Method 7 – __Referencing the Totals for Multiple Contiguous Columns Using an Excel Table Reference

In *Marks8*Â the reference of the totals will be used and the total value of the marks in *Physics *and *Chemistry *will be summed.

** Steps**:

Enterthe following formula in the

**C14Â**

`=SUM(Marks8[[#Totals],[Physics]:[Chemistry]])`

*Marks8* is the Table name, **[#Totals] **is the **Total **row, **[Physics]:[Chemistry] **is the range in the *Physics* and *Chemistry* column.

Press **ENTER** to see the sum of marks in *Physics* and *Chemistry*.

__Method 8 – __Referencing the Totals for Multiple Non-Contiguous Columns Using an Excel Table Reference

In *Marks9*Â the reference of the totals will be used and the total value of the marks in *Physics *and *Math* will be summed.

** Steps**:

Enter the following formula in

**C14.**

`=SUM(Marks9[[#Totals],[Physics]],Marks9[[#Totals],[Math]])`

*Marks9* is the Table name, **[#Totals] **is the **Total **row, **[Physics]**, and **[Math] **are the *Physics* and *Math* columns.

Press **ENTER**.

You will see the sum of the marks for the two non-contiguous columns *Physics* and *Math*.

__Method 9 – __Counting Total Rows and Columns Using an Excel Table Reference

Total rows and columns in*Â Marks10* will be counted by using the **ROWS **and **COLUMNS **functions.

** Steps**:

Enter the following formula in

**C13**

`=ROWS(Marks10)`

**ROWS **will determine the number of rows in *Marks10*.

Use the following formula in **C14**

`=COLUMNS(Marks10)`

**COLUMNS **will return the total number of columns inÂ *Marks10*.

This is the output.

__Method 10 – __Counting Blank and Non-Blank Cells Using an Excel Table Reference

The **COUNTBLANK function **and the **COUNTA function** will return the total blank and non-blank cells in the *Physics* column, in *Marks11*.

** Steps**:

Enter the following formula in

**C13.**

`=COUNTBLANK(Marks11[Physics])`

**COUNTBLANK **will determine the number of blank cells in the *Physics* column.

Use the following formula in **C14**

`=COUNTA(Marks11[Physics])`

**COUNTA **will determine the number of non-blank cells in the *Physics* column.

This is the output.

## Practice Section

Practice here.

**Download Workbook**

## Structured Reference Excel: Knowledge Hub

- Applications of Absolute Structured References with Table Formulas
- Use HLOOKUP with Structured Reference
- Lock a Structured Reference
- Reference a Dynamic Component of a Structured Reference
- What is an Unqualified Structured Reference
- Use IF Function and Structured Reference

**<< Go Back to Table Formula | Excel Table | Learn Excel**