Selecting cells, rows, and columns in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula selecting a range?

In Google Sheets, what will be the result of entering the formula =SUM(B2:D2) if the cells B2, C2, and D2 contain the values 5, 10, and 15 respectively?

Google Sheets

=SUM(B2:D2)

A 10

B 15

C 30

D 5

💡 Hint

Think about what the SUM function does with a range of cells.

The formula sums all values in cells B2, C2, and D2. Since they are 5, 10, and 15, the total is 30.

❓ Function Choice

intermediate

Which formula selects the entire third row?

You want to select all cells in the third row of a Google Sheet to sum their values. Which formula correctly selects the entire third row?

A =SUM(3:3)

B =SUM(C3:C)

C =SUM(A3:Z3)

D =SUM(3:C)

💡 Hint

Selecting a whole row uses the row number before and after the colon.

Using 3:3 selects the entire third row. Other options either select a column or are invalid.

📊 Formula Result

advanced

What is the output of this mixed cell selection formula?

Given the following values in Google Sheets:
- A1 = 2
- B1 = 3
- C1 = 4
What is the result of the formula =SUM(A1, B1:C1)?

Google Sheets

=SUM(A1, B1:C1)

A Error

B 7

C 5

D 9

💡 Hint

SUM can take single cells and ranges together.

The formula sums A1 (2) plus the range B1:C1 (3 + 4), total 2 + 3 + 4 = 9.

🎯 Scenario

advanced

Selecting columns with dynamic ranges

You have a Google Sheet where column A has names and columns B to E have monthly sales data. You want a formula that sums all sales for the first person in row 2, but the number of months (columns) can change. Which formula correctly sums all sales from column B to the last column with data in row 2?

A =SUM(B2:OFFSET(B2,0,COUNTA(2:2)-1))

B =SUM(B2:INDEX(2:2, COUNTA(2:2)))

C =SUM(B2:E2)

D =SUM(B2:Z2)

💡 Hint

Use INDEX with COUNTA to find the last filled column in the row.

Option B uses INDEX to find the last non-empty cell in row 2 and sums from B2 to that cell, adapting to changing columns.

🧠 Conceptual

expert

How many cells are selected by this range?

In Google Sheets, what is the total number of cells selected by the range C3:E5?

A 9

B 6

C 12

D 15

💡 Hint

Count how many rows and columns are included in the range.

The range C3:E5 covers 3 columns (C, D, E) and 3 rows (3, 4, 5), so 3 x 3 = 9 cells.