Table references in formulas in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate total sales using structured table references

Given an Excel table named SalesData with columns Product and Amount, what is the result of the formula =SUM(SalesData[Amount]) if the Amount column contains values 100, 200, and 300?

A SUM(SalesData)

B 100

C 600

D 3

💡 Hint

The formula sums all numbers in the Amount column of the table.

The formula =SUM(SalesData[Amount]) adds all values in the Amount column of the SalesData table: 100 + 200 + 300 = 600.

❓ Function Choice

intermediate

Choose the correct formula to count rows in a table

You have a table named Employees with a column Name. Which formula correctly counts how many rows are in the table?

A =COUNTA(Employees[Name])

B =ROWS(Employees)

C =COUNT(Employees[Name])

D =COUNTIF(Employees, "*")

💡 Hint

Counting non-empty cells in a column counts rows.

COUNTA counts all non-empty cells in the Name column, effectively counting rows. ROWS(Employees) returns the number of rows including header and totals which is not valid for tables. COUNT counts only numbers, so it may miss text names. COUNTIF with a table reference is invalid syntax.

📊 Formula Result

advanced

Result of formula using table and column references with filters

In a table named Orders with columns Status and Amount, the Status column has values: "Complete", "Pending", "Complete" and the Amount column has values: 150, 200, 100. What is the result of the formula =SUMIFS(Orders[Amount], Orders[Status], "Complete")?

A 450

B 250

C 200

D Syntax Error

💡 Hint

SUMIFS sums amounts where Status equals "Complete".

The formula sums Amount values where Status is "Complete": 150 + 100 = 250.

🎯 Scenario

advanced

Identify the error in a formula using table references

You wrote the formula =SUM(Table1[Price]) + Table1[Quantity] in a cell. What will happen when you enter this formula?

A It returns a #VALUE! error because you cannot add a range to a range directly

B It returns the sum of Price column plus the sum of Quantity column

C It returns the sum of Price column only

D It returns the sum of Quantity column only

💡 Hint

Adding two ranges directly causes an error.

You cannot add two ranges directly in Excel formulas. Adding Table1[Price] + Table1[Quantity] causes a #VALUE! error because Excel does not know how to add arrays without aggregation.

❓ data_analysis

expert

Determine the number of unique products sold using table references

Given a table named Sales with a column Product containing: "Apple", "Banana", "Apple", "Orange", "Banana", which formula correctly returns the number of unique products sold?

A =COUNTA(Sales[Product])

B =COUNTIF(Sales[Product], "<>Apple")

C =COUNT(Sales[Product])

D =SUMPRODUCT(1/COUNTIF(Sales[Product], Sales[Product]))

💡 Hint

Use a formula that counts unique text values in a column.

The formula =SUMPRODUCT(1/COUNTIF(Sales[Product], Sales[Product])) counts unique values by dividing 1 by the count of each product and summing these fractions, resulting in 3 unique products: Apple, Banana, Orange.

Practice

1. What does a table reference like Sales[Amount] mean in Excel?

easy

A. It refers to a cell named 'Amount' in the worksheet.

B. It refers to the entire 'Sales' table.

C. It refers to a range named 'SalesAmount'.

D. It refers to the 'Amount' column in the 'Sales' table.

Solution

1. Step 1: Understand table and column naming

   In Excel, Sales[Amount] means the 'Amount' column inside the table named 'Sales'.

2. Step 2: Differentiate from other references

   This is different from referring to the whole table or a named range; it specifically targets one column.

3. Final Answer:

   It refers to the 'Amount' column in the 'Sales' table. -> Option D

4. Quick Check:

   TableName[ColumnName] = Column in Table [OK]

Hint: TableName[ColumnName] always means one column in that table [OK]

Common Mistakes:

• Thinking it refers to the whole table
• Confusing with named ranges
• Assuming it refers to a single cell

2. Which of these is the correct syntax to sum the 'Price' column in a table named 'Products'?

easy

A. =SUM(Products.Price)

B. =SUM(Products[Price])

C. =SUM(Products{Price})

D. =SUM(Products-Price)

Solution

1. Step 1: Recall correct table reference syntax

   Excel uses square brackets to refer to columns inside tables, so Products[Price] is correct.

2. Step 2: Check formula structure

   The SUM function wraps the table column reference correctly as =SUM(Products[Price]).

3. Final Answer:

   =SUM(Products[Price]) -> Option B

4. Quick Check:

   SUM(TableName[ColumnName]) = Correct sum formula [OK]

Hint: Use square brackets for columns inside tables in formulas [OK]

Common Mistakes:

• Using dot notation instead of brackets
• Using curly braces instead of brackets
• Using minus sign instead of brackets

3. Given a table named Orders with a column Quantity containing values 2, 5, 3, what is the result of =SUM(Orders[Quantity])?

medium

A. 2

B. Error

C. 10

D. 5

Solution

1. Step 1: Identify values in the 'Quantity' column

   The values are 2, 5, and 3 in the 'Quantity' column of the 'Orders' table.

2. Step 2: Calculate the sum of these values

   Sum = 2 + 5 + 3 = 10.

3. Final Answer:

   10 -> Option C

4. Quick Check:

   SUM(2,5,3) = 10 [OK]

Hint: Sum adds all values in the specified table column [OK]

Common Mistakes:

• Thinking it returns a single cell value
• Expecting an error due to table reference
• Adding only the first value

4. You wrote the formula =SUM(Sales[Amount] but it shows an error. What is the likely fix?

medium

A. Add the missing closing parenthesis: =SUM(Sales[Amount])

B. Change brackets to parentheses: =SUM(Sales(Amount))

C. Remove the table name: =SUM([Amount])

D. Replace SUM with SUMIF: =SUMIF(Sales[Amount])

Solution

1. Step 1: Check formula syntax

   The formula is missing a closing parenthesis at the end, causing a syntax error.

2. Step 2: Correct the formula by adding the missing parenthesis

   Adding the closing parenthesis fixes the syntax: =SUM(Sales[Amount]).

3. Final Answer:

   Add the missing closing parenthesis: =SUM(Sales[Amount]) -> Option A

4. Quick Check:

   Balanced parentheses fix formula errors [OK]

Hint: Always check for matching parentheses in formulas [OK]

Common Mistakes:

• Using wrong bracket types
• Removing table name incorrectly
• Changing function without reason

5. You have a table named Inventory with columns Item and Stock. You want to calculate total stock only for items named "Apple" using a formula with table references. Which formula is correct?

hard

A. =SUMIFS(Inventory[Stock], Inventory[Item], "Apple")

B. =SUM(Inventory[Stock] = "Apple")

C. =SUMIF(Inventory[Stock], "Apple", Inventory[Item])

D. =SUMIF(Inventory[Item], Inventory[Stock], "Apple")

Solution

1. Step 1: Understand the goal and function

   You want to sum the 'Stock' column only where 'Item' equals "Apple". The correct function for multiple criteria is SUMIFS.

2. Step 2: Check formula syntax with table references

   The correct syntax is =SUMIFS(Inventory[Stock], Inventory[Item], "Apple"), summing 'Stock' where 'Item' matches "Apple".

3. Final Answer:

   =SUMIFS(Inventory[Stock], Inventory[Item], "Apple") -> Option A

4. Quick Check:

   SUMIFS(sum_range, criteria_range, criteria) with table refs [OK]

Hint: Use SUMIFS with table columns for conditional sums [OK]

Common Mistakes:

• Mixing SUMIF arguments order
• Using SUM instead of SUMIFS for conditions
• Swapping criteria and sum ranges