Spreadsheet Practice

How to calculate percent of total with ARRAYFORMULA in Google Sheets

Google Sheets formula

=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10)))

One formula at the top fills the column: =ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10))).

Result on the example
a 9-row list that spills from C2 in C2
Excel
See below

See it work

The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.

How it works

ARRAYFORMULA makes the division work on the whole range at once: each revenue in B2:B10 is divided by the total, and the results fill down from C2. The IF leaves a blank where there is no revenue yet, so new rows typed into B7:B10 get a share automatically.

=ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10)))

Result{9 rows × 1 col} 0.4, 0.2666666667, 0.15, 0.1, …

  1. B2:B109 rows × 1 col: $120, $80, $45, $30, …

    A range: 9 cells in one column.

  2. B2:B10=""{9 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …

    The = operator tests whether the two sides are equal.

  3. SUM(B2:B10)300

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1B2:B109 rows × 1 col: $120, $80, $45, $30, …
  4. B2:B10/SUM(B2:B10){9 rows × 1 col} 0.4, 0.2666666667, 0.15, 0.1, …

    The / operator divides.

  5. IF(B2:B10="","",B2:B10/SUM(B2:B10)){9 rows × 1 col} 0.4, 0.2666666667, 0.15, 0.1, …

    IF: Returns one value if a test is true, another if false.

    • logical_testB2:B10=""{9 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
    • value_if_true""
    • value_if_falseB2:B10/SUM(B2:B10){9 rows × 1 col} 0.4, 0.2666666667, 0.15, 0.1, …
  6. ARRAYFORMULA(IF(B2:B10="","",B2:B10/SUM(B2:B10))){9 rows × 1 col} 0.4, 0.2666666667, 0.15, 0.1, …

    ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.

    • array_formulaIF(B2:B10="","",B2:B10/SUM(B2:B10)){9 rows × 1 col} 0.4, 0.2666666667, 0.15, 0.1, …

In Excel

In Excel 365 the same formula works without ARRAYFORMULA and spills down: =IF(B2:B10="","",B2:B10/SUM(B2:B10)). In older versions, use =B2/SUM($B$2:$B$10) and fill down.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ARRAYFORMULA(B2:B10/SUM(B2:B10))spills 9 × 1Different resultWithout the IF, empty rows show 0%. Fine for a range with no gaps. Shows 0.0% in the three empty rows.

Common mistakes

  • Anything typed in C3:C10 blocks the result and shows #REF!. Leave the column below the formula empty.
  • An open-ended range like B2:B works, but then SUM(B2:B) also includes any total row you add at the bottom.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate percent of total with ARRAYFORMULA

In C2, write one formula that calculates every channel's share of total revenue down the column, leaving blank rows blank.

Questions

Do I need ARRAYFORMULA in Excel?

No. Excel 365 spills array results automatically; ARRAYFORMULA is a Google Sheets function.

How do I round the shares?

Wrap the division: =ARRAYFORMULA(IF(B2:B10="","",ROUND(B2:B10/SUM(B2:B10),3))).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.