Articles

How do you calculate a weighted average in Excel with SUMPRODUCT?

Multiply each value by its weight, add the products, divide by the sum of the weights. Two functions, one line, and three ways to get it wrong.

A weighted average in Excel is =SUMPRODUCT(values,weights)/SUM(weights). SUMPRODUCT multiplies each value by its weight and adds the products; the division by the total weight turns the sum into an average. On the rent roll of this example, the weighted average lease term of the portfolio is 3.72 years. A plain average of the same leases gives 3.05 years, and nothing in the cell says which one you are looking at.

The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →

The formula, on one property

Alder House, in the fictional Brackenridge portfolio, has five units. Four are let; one is vacant.

Alder House: remaining term weighted by passing rent, 31 December 2026.
UnitTenantRent (EUR k)Term (years)Rent x term
A01Northgate Legal372.02.50930.0
A02Pellam Software255.00.75191.2
A03Vacant0.00.000.0
A04Orrin Consulting304.04.251,292.0
A05Corner Cafe60.02.00120.0
Total991.02,533.2

2,533.2 divided by 991.0 gives 2.56 years. The vacant unit has no rent, so it carries no weight. In one cell, with the rent in G5:G9 and the term in J5:J9: =SUMPRODUCT(G5:G9,J5:J9)/SUM(G5:G9).

One formula for every property

When the table holds several properties, add a condition inside SUMPRODUCT and use SUMIFS for the weights:

=SUMPRODUCT((Data!$C$5:$C$24=D$4)*Data!$G$5:$G$24*Data!$J$5:$J$24)/SUMIFS(Data!$G$5:$G$24,Data!$C$5:$C$24,D$4)

The test Data!$C$5:$C$24=D$4 returns 1 on the rows of the property named in D4 and 0 elsewhere. The three ranges must have the same height, or the formula returns an error.

Three averages, three answers

Brackenridge portfolio (fictional): average remaining lease term in years, three ways.
MethodAlder HouseBirch CourtCedar YardDunmore ParkPortfolio
Weighted by rent (WALT)2.562.874.704.803.72
Plain average of the let units2.382.003.004.253.05
Weighted by area2.532.864.644.804.19

The three lines rarely agree. At Cedar Yard, the plain average is 3.00 years against 4.70 weighted by rent. For the portfolio, the same leases give 3.72, 3.05 or 4.19 years. Each figure is a correct answer to a different question: state the weight in the label of the line.

The total is not the average of the columns

The portfolio figure, 3.72 years, weights every lease by its rent. Averaging the four property figures gives 3.73 years. The two are close here by coincidence: an average of subtotals gives the smallest property the same weight as the largest, and the gap opens as soon as the properties differ in size and in lease length. Compute a total from the rows, never from the subtotals.

Three ways to get it wrong

Asking an assistant for this formula

Say what the weight is. "Average lease term by property" returns AVERAGEIFS. "Average remaining term weighted by passing rent, rent in column G, term in column J, rows 5 to 24, property name in D4, copied to the right" returns the formula above. Then test it: on four invented leases where X has 100 of rent for 2 years and 200 for 5 years, the answer must be 4.00, not 3.50.

Sources

  1. Julian R. Sterling, AI in Excel for Analysts (video course, 2026): running case Brackenridge portfolio
  2. Microsoft Support, function reference for SUMPRODUCT, SUMIFS, COUNTIFS, VALUE, SUBSTITUTE, TRIM and DATE
  3. ICAEW, Twenty principles for good spreadsheet practice

Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.

Formulas you can prove, step by step

26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).

Watch module 1 free →

Questions readers ask

What is the Excel formula for a weighted average?

SUMPRODUCT of the values and the weights, divided by the SUM of the weights: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). Excel has no single weighted average function.

How do you calculate a weighted average with a condition in Excel?

Put the condition inside SUMPRODUCT as a test that returns 1 or 0, multiplied by the values and the weights, and divide by a SUMIFS of the weights on the same condition.

Why does my weighted average differ from the average of the subtotals?

Because averaging subtotals gives each group the same weight. A weighted total must be computed from the underlying rows, with each row carrying its own weight.

Read the whole case

This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.

See the course →Free companion files

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada

Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99

Also on this site

All 2221 articles →