Fix ARRAYFORMULA size mismatches

What you will build

Complete “Fix ARRAYFORMULA size mismatches” using the visible A, B, Result sample so each action and the final state can be checked against the source data.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Complete Google Sheets source table before formula verification.

Hands-on practice

Step 1: Inspect A2:A4 and B2:B4 and notice the final B cell is blank.

Step 2: Evaluate =ARRAYFORMULA(A2:A4+IFNA(B2:B4,0)) in C2. One formula should spill three results: 11, 22, and 3.

Step 3: Confirm the formula exists only in C2 while C3:C4 are spill outputs. Do not fill ARRAYFORMULA down manually.

=ARRAYFORMULA(A2:A4+IFNA(B2:B4,0))

The result cell is selected with the formula visible in the formula bar and the complete source table available for comparison.

How this formula works=ARRAYFORMULA(A2:A4+IFNA(B2:B4,0))

Overall meaning of the whole formula
- Add corresponding elements of A2:A4 and B2:B4 in one array formula; when an element on the B side is #N/A, replace that error with 0 before addition.
ARRAYFORMULA(array_formula)
- array_formula is required. It is the range or expression that ARRAYFORMULA evaluates as an array.
- It can be a range such as A2:A4; an array expression using operators such as +, -, *, /, or &; or a function expression that uses ranges as inputs.
- In this lesson, array_formula is A2:A4 + IFNA(B2:B4, 0).
- Sheets evaluates it by matching rows: A2 + IFNA(B2,0), A3 + IFNA(B3,0), and A4 + IFNA(B4,0). This is why A2:A4 and B2:B4 need compatible dimensions.
IFNA(value, value_if_na)
- value is required. Here it is B2:B4. Inside ARRAYFORMULA, IFNA checks the range element by element: B2, B3, then B4.
- value_if_na is also required. Here it is 0, and it is used only when the corresponding value evaluates to #N/A.
- B2=10 and B3=20 are ordinary numbers, so IFNA keeps them unchanged.
- B4 is blank, not #N/A, so IFNA does not replace B4 with 0. The 0 parameter is only the fallback for #N/A.
How the complete expression is calculated
- After IFNA is evaluated, Sheets performs the + operation row by row.
- Row 2: A2=1 and B2=10 → 1 + 10 = 11.
- Row 3: A3=2 and B3=20 → 2 + 20 = 22.
- Row 4: A4=3 and B4 is blank → in numeric addition the blank contributes 0 → 3 + 0 = 3.
- The three row results therefore form the array {11;22;3}.
Why the result spills as 11, 22, 3
- ARRAYFORMULA returns the three-item array from a single formula cell and spills it down the Result column.
- You enter the formula once; Sheets produces the three results automatically instead of requiring one formula per row.
- If the participating ranges do not have compatible dimensions, their elements cannot be paired row by row as intended, which is the size-mismatch problem this lesson is designed to prevent.

Enter the formula in C2.

Checkpoint

A	B	Result
1	10	11
2	20	22
3		3

For formula lessons, result cells must be calculated rather than typed manually.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the A, B, Result source fields.

Independent practice

Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Key takeaways

✓Complete “Fix ARRAYFORMULA size mismatches” using the visible A, B, Result sample so each action and the final state can be checked against the source data.
✓Inspect A2:A4 and B2:B4 and notice the final B cell is blank.
✓Evaluate =ARRAYFORMULA(A2:A4+IFNA(B2:B4,0)) in C2. One formula should spill three results: 11, 22, and 3.
✓If the result differs, repeat “Inspect A2:A4 and B2:B4 and notice the final B cell is blank.” and then “Evaluate =ARRAYFORMULA(A2:A4+IFNA(B2:B4,0)) in C2. One formula should spill three results: 11, 22, and 3.”. Verify the working table still uses A, B, Result before changing source values.
✓Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Next lesson

Continue in recommended learning order with Set up a clean sheet for Book collection.

What you will build

Complete “Fix ARRAYFORMULA size mismatches” using the visible A, B, Result sample so each action and the final state can be checked against the source data.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Hands-on practice

Step 1: Inspect A2:A4 and B2:B4 and notice the final B cell is blank.

Step 2: Evaluate =ARRAYFORMULA(A2:A4+IFNA(B2:B4,0)) in C2. One formula should spill three results: 11, 22, and 3.

Step 3: Confirm the formula exists only in C2 while C3:C4 are spill outputs. Do not fill ARRAYFORMULA down manually.

=ARRAYFORMULA(A2:A4+IFNA(B2:B4,0))

How this formula works=ARRAYFORMULA(A2:A4+IFNA(B2:B4,0))

Overall meaning of the whole formula
- Add corresponding elements of A2:A4 and B2:B4 in one array formula; when an element on the B side is #N/A, replace that error with 0 before addition.
ARRAYFORMULA(array_formula)
- array_formula is required. It is the range or expression that ARRAYFORMULA evaluates as an array.
- It can be a range such as A2:A4; an array expression using operators such as +, -, *, /, or &; or a function expression that uses ranges as inputs.
- In this lesson, array_formula is A2:A4 + IFNA(B2:B4, 0).
- Sheets evaluates it by matching rows: A2 + IFNA(B2,0), A3 + IFNA(B3,0), and A4 + IFNA(B4,0). This is why A2:A4 and B2:B4 need compatible dimensions.
IFNA(value, value_if_na)
- value is required. Here it is B2:B4. Inside ARRAYFORMULA, IFNA checks the range element by element: B2, B3, then B4.
- value_if_na is also required. Here it is 0, and it is used only when the corresponding value evaluates to #N/A.
- B2=10 and B3=20 are ordinary numbers, so IFNA keeps them unchanged.
- B4 is blank, not #N/A, so IFNA does not replace B4 with 0. The 0 parameter is only the fallback for #N/A.
How the complete expression is calculated
- After IFNA is evaluated, Sheets performs the + operation row by row.
- Row 2: A2=1 and B2=10 → 1 + 10 = 11.
- Row 3: A3=2 and B3=20 → 2 + 20 = 22.
- Row 4: A4=3 and B4 is blank → in numeric addition the blank contributes 0 → 3 + 0 = 3.
- The three row results therefore form the array {11;22;3}.
Why the result spills as 11, 22, 3
- ARRAYFORMULA returns the three-item array from a single formula cell and spills it down the Result column.
- You enter the formula once; Sheets produces the three results automatically instead of requiring one formula per row.
- If the participating ranges do not have compatible dimensions, their elements cannot be paired row by row as intended, which is the size-mismatch problem this lesson is designed to prevent.

Enter the formula in C2.

Checkpoint

A	B	Result
1	10	11
2	20	22
3		3

For formula lessons, result cells must be calculated rather than typed manually.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the A, B, Result source fields.

Independent practice

Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Key takeaways

✓Complete “Fix ARRAYFORMULA size mismatches” using the visible A, B, Result sample so each action and the final state can be checked against the source data.
✓Inspect A2:A4 and B2:B4 and notice the final B cell is blank.
✓Evaluate =ARRAYFORMULA(A2:A4+IFNA(B2:B4,0)) in C2. One formula should spill three results: 11, 22, and 3.
✓If the result differs, repeat “Inspect A2:A4 and B2:B4 and notice the final B cell is blank.” and then “Evaluate =ARRAYFORMULA(A2:A4+IFNA(B2:B4,0)) in C2. One formula should spill three results: 11, 22, and 3.”. Verify the working table still uses A, B, Result before changing source values.
✓Add one row with the same schema, repeat the action, verify the new row behaves correctly, then remove it to restore the sample.

Next lesson

Continue in recommended learning order with Set up a clean sheet for Book collection.

Fix ARRAYFORMULA size mismatches

What you will build

Learning objectives

Open the practice workbook

Practice in your own copy

Hands-on practice

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Next lesson

What should you learn next?

Comments

Fix ARRAYFORMULA size mismatches

What you will build

Learning objectives

Open the practice workbook

Practice in your own copy

Hands-on practice

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Next lesson

What should you learn next?

Comments