Last Updated: 22/07/2026
If you work with spreadsheets regularly, you’ve probably repeated the same formula across dozens of cells just to get a single grand total. Array formulas in LibreOffice Calc solve that exact problem – letting you perform calculations across an entire range of data in a single step. This tutorial (Part 1 of a series from DCP Web Designers) walks through the fundamentals of array formulas and the powerful SUMPRODUCT function, using practical, real-world examples.
What Is an Array Formula?
An array formula lets you apply one calculation to multiple values at once, instead of writing (and dragging) a separate formula for every cell. The key difference from a normal formula is how you enter it.
The Golden Rule: Ctrl + Shift + Enter
To create an array formula in LibreOffice Calc:
- Select the cells where you want the results to appear.
- Type your formula as usual.
- Instead of pressing Enter, hold down Ctrl + Shift and then press Enter.
This tells Calc to treat the formula as an array operation across the whole selected range rather than a single cell.
⚠️ Note: You must select the destination cells before entering the formula for this to work correctly.
Example 1: Calculating Percentages the Array Way
Say you have six values — 100, 200, 300, 50, 80, 90 – and you want 20% of each one.
The manual (slow) way:
- =A4*0.2, then drag/series-fill down for each row
- Repeat for the second column
- That’s 6 separate calculations
The array formula way:
- Highlight the entire block of source cells (e.g., A4:B6)
- Type = then reselect that same block
- Type *0.2
- Press Ctrl + Shift + Enter
Instantly, every cell in the range is calculated – all in one formula instead of six.
Editing an Array Formula
Array formulas behave differently than normal ones. If you try to double-click and edit just one cell inside the array, LibreOffice will block you with an error: “You cannot change only part of an array.”
To edit it, you must:
Select the entire array range again
Click into the formula bar
Make your change
Re-confirm with Ctrl + Shift + Enter
For example, to add 20% to a set of values instead of just calculating 20%, you’d select the array, change the formula to *1.2, and re-enter it the same way.
Example 2: The SUMPRODUCT Function
SUMPRODUCT is a built-in array-style function – and unlike manual array formulas, it does not require Ctrl + Shift + Enter. Just press Enter normally.
Multiplication with SUMPRODUCT
Given two columns of numbers (e.g., 1, 2, 3 and 4, 5, 6), if you wanted to multiply each pair and sum the totals manually, you’d need:
- 3 multiplication formulas
- 1 SUM formula
- = 4 separate calculations
With SUMPRODUCT, it’s one step:
=SUMPRODUCT(A10:A12, B10:B12)
This multiplies each row pair together and sums the results – all in a single formula, returning the same answer (e.g., 32) that four manual steps would produce.
Subtraction with SUMPRODUCT
=SUMPRODUCT(B17:B19) - SUMPRODUCT(A17:A19)
This sums column B, sums column A, and subtracts the two – dynamically recalculating any time the source values change.
Real-World Example: Monthly Profit Calculations
The tutorial ties everything together with a business scenario: 12 months of costs and revenue, where you need to calculate profit for each month, the total profit, and the average monthly profit.
Step 1 - Total Profit in One Formula
Instead of calculating profit for each of the 12 months individually and then summing them (13 calculations), a single array formula does it all:
=SUMPRODUCT(Revenue_Range) - SUMPRODUCT(Cost_Range)
Step 2 - Average Profit, Three Ways
The tutorial demonstrates three methods to calculate average monthly profit, all landing on the same result:
- Manual division: Calculate the average of revenue and average of costs separately, then subtract.
- AVERAGE function: =AVERAGE(Profit_Range) across the already-calculated profit column.
- Array formula with AVERAGE:
=AVERAGE(Revenue_Range – Cost_Range)
entered with Ctrl + Shift + Enter – calculating the average of the difference between two entire ranges in a single formula.
All three methods return the identical average (e.g., £3,300), but the array approach eliminates the need for helper columns or intermediate calculations entirely.
Why This Matters
The core takeaway is efficiency:
- Fewer helper columns and formulas cluttering your spreadsheet
- Automatic recalculation – change any source value and every array formula updates instantly
- Faster analysis, whether you’re tracking business costs and revenue, or personal expenses like gas, electric, and water bills
Array formulas and SUMPRODUCT can feel unusual at first because of the Ctrl+Shift+Enter step, but the more you practice, the more intuitive they become – and the time savings scale dramatically as your datasets grow.
What's Next?
This is Part 1 of a series on LibreOffice Calc array formulas and functions. Future tutorials will build on these fundamentals with more advanced use cases.
Don’t have LibreOffice yet? It’s a free, open-source alternative to Excel – check the linked installation tutorial to get set up before diving in.
