Mixed references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula with mixed references?

Given the formula =A$1 + $B2 entered in cell C3, and the following values:

- A1 = 10
- B2 = 5
- A3 = 3
- B3 = 7

What will be the value of the formula in cell C3?

A 22

B 17

C 15

D 12

💡 Hint

Remember that $ locks either the row or the column depending on its position.

The formula in C3 is =A$1 + $B2. A$1 refers to A1 (10). $B2 refers to B2 (5). The sum is 10 + 5 = 15. (A3 and B3 are distractors; relative references adjust only upon copying, not at entry.)

❓ Function Choice

intermediate

Which formula correctly sums a fixed column with a changing row?

You want to sum values from column C, but the row number should change as you copy the formula down. Which formula uses mixed references correctly to achieve this?

A =SUM(C$1)

B =SUM($C1)

C =SUM($C$1)

D =SUM(C1)

💡 Hint

Think about which part of the reference should stay fixed and which should change.

The formula =SUM($C1) locks the column C but allows the row to change as you copy down. So in row 2 it becomes $C2, in row 3 $C3, etc. This is the correct mixed reference for summing down a fixed column.

❓ data_analysis

advanced

How many unique cells are referenced when copying this formula?

If you enter the formula =B$2 + $C3 in cell D4 and copy it down 3 rows and right 2 columns (to cells E4:F7), how many unique cells are referenced in total by all copied formulas?

A 6

B 9

C 8

D 12

💡 Hint

Track how the mixed references change when copying across rows and columns.

Copied to E4:F7 (including original D4). E column formulas reference C$2 + $C3/$C4/$C5/$C6. F column: D$2 + $C3/$C4/$C5/$C6. Unique cells: C2, D2, C3, C4, C5, C6 (6 total).

🎯 Scenario

advanced

You want to multiply a fixed row by a fixed column in a table

You have a table where row 1 contains prices and column A contains quantities. You want to create a formula in cell B2 that multiplies the quantity in column A by the price in row 1, and then copy this formula across the table. Which formula uses mixed references correctly?

A =$A2*B$1

B =A$2*$B1

C =$A$2*$B$1

D =A2*B1

💡 Hint

Lock the column for quantities and lock the row for prices.

=$A2*B$1 locks column A for quantities (so it always refers to column A) and locks row 1 for prices (so it always refers to row 1). The row for quantities and column for prices change as you copy the formula across the table.

📊 Formula Result

expert

What is the value after copying this formula with mixed references?

You enter the formula =SUM($A2:B$1) in cell C3. What is the result of this formula if:
- A1=1, B1=2
- A2=3, B2=4
- A3=5, B3=6

Assuming the formula is copied from C3 to D4, what is the value in D4?

A 9

B 12

C 10

D 7

💡 Hint

Check how the mixed references shift when copying one row down and one column right.

Original formula in C3: =SUM($A2:B$1). In D4 (one row down, one column right), formula becomes =SUM($A3:C$1). This range includes A3 and B1 (since C1 is empty). Sum is 5 + 2 = 7.