=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C)) ARRAYFORMULA enables non-array formulas to process full ranges and spill results down entire columns. Always combine with IF(A2:A="", "", ...) to prevent blank rows from filling with zeros.
Google Sheets ARRAYFORMULA
In Google Sheets, instead of dragging a formula down 5,000 rows (which slows down the browser), ARRAYFORMULA allows a single cell formula in row 2 to calculate the entire column automatically. When new form submissions or rows are added, they calculate instantly!
1. Automatic Column Multiplication
To calculate Total Price (Quantity * Unit Price) for the entire column:
=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C))IF(A2:A="", "", ...): Ensures empty rows stay blank rather than displaying$0.00.
2. ARRAYFORMULA with VLOOKUP
To lookup prices for every row in an order log with a single formula:
=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, PriceList!A:B, 2, FALSE)))Frequently Asked Questions
Why do AND / OR functions break inside ARRAYFORMULA in Google Sheets?
AND() and OR() aggregate entire ranges into a single TRUE/FALSE result. Inside ARRAYFORMULA, replace AND with multiplication (*) and replace OR with addition (+).
What is the shortcut to wrap a formula in ARRAYFORMULA?
Press Ctrl + Shift + Enter (Windows) or Cmd + Shift + Enter (Mac) while editing a formula.