The Hidden Gem of Excel

Excel SUMPRODUCT Function Explained

SUMPRODUCT does more than you think

By Sharanmeet Singh••
Tags:ExcelSUMPRODUCTWeighted AverageFormulas

Excel Formula Generator

Generate Excel formulas for data analysis and calculations

Try Generator

SUMPRODUCT Function

The SUMPRODUCT() function multiplies and sums arrays.

Basic Example

=SUMPRODUCT(A2:A5,B2:B5)

Multiplies pairs and sums → weighted total.

Conditional Example

=SUMPRODUCT((A2:A10="East")*(B2:B10))

Sums values where region = East.

Use Cases

  • Weighted averages
  • Multi-condition sums
  • Replacing complex array formulas

Pro Tip: SUMPRODUCT can act as an advanced COUNTIF or SUMIF replacement.

Excel SUMPRODUCT Function Explained | QueryBoss