Why is SUMPRODUCT so slow?

It calculates every row of the ranges given. With whole columns, that is a million rows per range.

The SUMPRODUCT function

Multiplying columns row by row, then adding up: the quantity × price total with no helper column. And, bent to other uses, the way to count or add up with any condition.

Syntax

=SUMPRODUCT(array1, [array2], …)

In a French Excel: =SOMMEPROD(matrice1; [matrice2]; …).

Arguments

array1, array2…: ranges of the same size, multiplied row by row.

An example

=SUMPRODUCT((A2:A500="North")*(B2:B500>1000)*C2:C500)

Each condition is 1 when true, 0 otherwise: the formula adds up C on the North rows where B exceeds 1,000.

Pitfalls

Whole columns (A:A) make it calculate a million rows: limit the ranges.
Ranges of different sizes: #VALUE!.

Going further

SUMIFS, simpler when the criteria are equalities.
Explain a formula: flags whole columns.

Frequently asked questions

Why is SUMPRODUCT so slow?: It calculates every row of the ranges given. With whole columns, that is a million rows per range.