# How to Use an Excel Table Reference -10 Methods

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.

### 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 here.

## Structured Reference Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF