Array Formulasintermediate
Calculate a weighted average with SUMPRODUCT
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
Array Formulas
Array Formulas formulas
Use dynamic arrays to filter, sort, deduplicate, and reshape data without helper columns.
Array Formulasbeginner
Extract a unique list with UNIQUE
=UNIQUE(A2:A100)
Array Formulasintermediate
Filter rows by a condition with FILTER
=FILTER(A2:C100,B2:B100="East","No results")
Array Formulasbeginner
Generate a number sequence with SEQUENCE
=SEQUENCE(10)
Array Formulasadvanced
Reuse calculations in one formula with LET
=LET(rate,B1/12,periods,B2*12,PMT(rate,periods,-B3))
Array Formulasbeginner
Sort a range dynamically with SORT
=SORT(A2:B20,2,-1)
Frequently asked questions
How do I use SUMPRODUCT as a conditional count?
Use boolean arrays — =SUMPRODUCT((A2:A100="East")*(B2:B100>1000)) counts rows where region is East AND amount exceeds 1000, with no Ctrl+Shift+Enter needed.
How do I sort the unique list alphabetically?
Wrap in SORT — =SORT(UNIQUE(A2:A100)) — to return the distinct values in ascending order.
How do I filter by multiple conditions?
Multiply the condition arrays for AND logic — =FILTER(A2:C100,(B2:B100="East")*(D2:D100>1000),"No results") returns rows where region is East AND amount exceeds 1000.
How do I generate a series of dates?
Use SEQUENCE with a date start and a step of 1 — =SEQUENCE(30,1,DATE(2026,1,1),1) generates 30 consecutive dates starting January 1, 2026. Format the cells as dates.