Dynamic arrays
DROP function: strip opening balances, headers, or total rows dynamically
Exclude N rows or columns from the start or end of an imported range with DROP so summary rows never double-count.
Cleanly remove wrapper rows from exported reports
Accounting exports often sandwich transaction lines between an Opening Balance row at the top and a Subtotal row at the bottom. DROP(array, rows, [columns]) removes rows from the top when rows > 0, or removes rows from the bottom when rows < 0.
Nesting DROP(DROP(B2:B6, 1), -1) strips both the opening balance and the trailing subtotal, leaving pure activity lines.
| Entry (A) | Amount (B) |
|---|---|
| Opening Balance | 1000 |
| Invoice 101 | 320 |
| Invoice 102 | 180 |
| Invoice 103 | 250 |
| Report Subtotal | 1750 |
Sum only transaction lines without manual range edits
Summing B2:B6 blindly would produce 3500 (counting both the opening balance and the subtotal twice). Dropping 1 row from the top and 1 row from the bottom totals only 320 + 180 + 250 = 750.
=SUM(DROP(DROP(B2:B6, 1), -1))Dropping every row returns #CALC!
If the number of dropped rows or columns equals or exceeds the size of the input range, nothing remains and DROP returns #CALC!.
=DROP(B2:B6, 5)Try the example
Use the original values or change them to test your understanding.