SUMPRODUCT and Weighted Averages in pandas

SUMPRODUCT is the Swiss army knife of Excel formulas: =SUMPRODUCT(B2:B7, C2:C7) multiplies quantities by prices and adds them up, =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7) does the same for one region, and =SUMPRODUCT(B2:B7,C2:C7)/SUM(B2:B7) is the usual way to write a weighted average. In pandas all three are vectorised arithmetic on columns, which is shorter and easier to read. The part that needs care is not the syntax but the weighted average itself — what to do with missing weights, how to do it per group, and why averaging averages gives a different, wrong answer. This page, part of Excel Formula Equivalents in pandas, covers all of it.

Prerequisites

pip install pandas openpyxl numpy

lines.xlsx has six order lines with units, price and margin. The East line has no units recorded.

Step 1 — SUMPRODUCT as multiply-then-sum

The direct translation multiplies the columns row by row and sums the products:

import numpy as np
import pandas as pd

lines = pd.read_excel("lines.xlsx")
total_value = (lines["units"] * lines["price"]).sum()
print(total_value)
# 1492.0

Multiplying two columns gives a new Series of products — the "value" column in the diagram — and sum() adds them, skipping the NaN from the East line, just as SUMPRODUCT treats an empty cell as zero. Keeping the product as a named column (lines["value"] = lines["units"] * lines["price"]) is often clearer still, because the intermediate figures can be checked and reused.

Series.dot computes the same thing in one call, but it does not skip missing values — the East line makes it return NaN. Prefer the multiply-and-sum form on real data.

Step 2 — Conditional SUMPRODUCT with boolean masks

Excel users put conditions into SUMPRODUCT by multiplying by comparisons that evaluate to 1 or 0. In pandas, filter with a boolean mask instead — the same logic, stated directly:

north = lines["region"] == "North"
print((lines.loc[north, "units"] * lines.loc[north, "price"]).sum())

cheap_widgets = (lines["product"] == "Widgets") & (lines["price"] < 2.75)
print((lines.loc[cheap_widgets, "units"] * lines.loc[cheap_widgets, "price"]).sum())

North's lines are worth 690. The second example combines two conditions with &, the equivalent of =SUMPRODUCT((B2:B7="Widgets")*(D2:D7<2.75)*C2:C7*D2:D7), and gives 250. For sums by every region at once, compute the product column and group it — lines.assign(value=lines["units"] * lines["price"]).groupby("region")["value"].sum() — rather than writing one mask per region. SUMIF and SUMIFS Equivalent in pandas covers conditional sums of a single column.

Counting with SUMPRODUCT conditions

Excel users also use SUMPRODUCT purely to count rows meeting several conditions — =SUMPRODUCT((A2:A7="North")*(C2:C7>10)) — because it worked before COUNTIFS existed and still handles conditions COUNTIFS cannot, such as comparisons between two columns. In pandas, a boolean mask summed counts its True values:

print(((lines["region"] == "North") & (lines["units"] > 10)).sum())
print((lines["units"] * lines["price"] > 200).sum(), "lines worth more than 200")

Two North lines have more than ten units, and three lines are worth more than 200. The second count compares a computed value, which COUNTIFS cannot do in Excel without a helper column; in pandas it is the same one-liner.

Step 3 — Weighted averages

The average price paid is not the mean of the price column; it is total value divided by total units, which weights each price by the units sold at it. That is SUMPRODUCT(units, price) / SUM(units):

simple = lines["price"].mean()
known = lines["units"].notna() & lines["price"].notna()
weighted = (lines.loc[known, "price"] * lines.loc[known, "units"]).sum() / lines.loc[known, "units"].sum()
print(f"simple mean price: {simple:.2f}   weighted by units: {weighted:.2f}")
print(np.average(lines.loc[known, "price"], weights=lines.loc[known, "units"]).round(2))

The simple mean is 11.90 because the expensive Gizmos, sold in tiny numbers, pull it up; the weighted average is 6.63, what customers actually paid per unit on average. numpy.average with weights computes the same figure. Build the known mask once and use it for both the numerator and the denominator: a row with a missing weight must drop out of both sums, or the average is computed over mismatched rows.

Step 4 — Weighted averages per group

A weighted average per region needs both sums per group. Compute the product column, sum both columns by group, and divide:

lines["value"] = lines["units"] * lines["price"]
by_region = lines.dropna(subset=["units"]).groupby("region")[["value", "units"]].sum()
by_region["avg_price"] = (by_region["value"] / by_region["units"]).round(2)
print(by_region)

Summing first and dividing afterwards is the key: it gives each region's true average price per unit. groupby().apply(lambda g: np.average(g["price"], weights=g["units"])) returns the same numbers but is slower and harder to extend when you also want the totals. East, whose only line has no units, drops out rather than showing a meaningless figure.

Step 5 — Do not average the averages

A company-wide average price calculated as the mean of the regional averages is wrong, for the same reason the simple mean was:

wrong = by_region["avg_price"].mean()
right = by_region["value"].sum() / by_region["units"].sum()
print(f"mean of regional averages: {wrong:.2f}   company-wide weighted: {right:.2f}")

The mean of the two regional averages is 6.77; the true company figure is 6.63. The gap is small here and can be large elsewhere — with a tiny, expensive region, the mean of averages can be off by a multiple. North sold 125 units and South 100, but the regional averages are 5.52 and 8.02 because their product mixes differ — averaging them ignores both effects. Any total row in a report of averages must be recomputed from the underlying sums, never averaged from the rows above it. The same applies to margins: an overall margin is total profit over total revenue, not the mean of the line margins.

Step 6 — Weighted margin, and writing the result

Margins are a common weighted average: the overall margin percentage is the revenue-weighted average of line margins. Compute it, then write the regional table with both the averages and the sums they came from, so readers can check the arithmetic:

lines["profit"] = lines["value"] * lines["margin"]
summary = lines.dropna(subset=["units"]).groupby("region")[["value", "profit", "units"]].sum()
summary["margin"] = (summary["profit"] / summary["value"]).round(3)
summary["avg_price"] = (summary["value"] / summary["units"]).round(2)
total = summary[["value", "profit", "units"]].sum()
summary.loc["Total"] = [*total, round(total["profit"] / total["value"], 3), round(total["value"] / total["units"], 2)]
print(summary)
summary.to_excel("weighted_summary.xlsx")

The Total row divides the summed profit by the summed value rather than averaging the margin column, so it is consistent with the rows above it. Add a Total Row to an Excel Table with Python shows how to add such totals as live formulas instead.

Keep the formula in the workbook when readers need it

A weighted average written as a static number goes stale when a reader changes a quantity. When the workbook is meant to be edited, write the Excel formula instead, next to the data pandas wrote:

import subprocess
from openpyxl import load_workbook

lines[["region", "product", "units", "price"]].to_excel("editable.xlsx", index=False)
wb = load_workbook("editable.xlsx")
ws = wb.active
last = ws.max_row
ws["G1"], ws["G2"] = "weighted avg price", f"=SUMPRODUCT(C2:C{last},D2:D{last})/SUM(C2:C{last})"
wb.save("editable.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "editable.xlsx"],
               check=True, capture_output=True, timeout=120)
print(round(load_workbook("calc/editable.xlsx", data_only=True).active["G2"].value, 2))

Recalculated, the formula gives 6.63, the same as the pandas figure: SUMPRODUCT treats the empty units cell as zero and SUM ignores it, so the East line drops out of both sides, exactly like the known mask.

Common pitfalls

• Series.dot on columns with gaps. It returns NaN; multiply and sum instead.
• Different rows in numerator and denominator. Mask missing weights once and use the mask for both sums.
• Averaging averages in total rows. Recompute from the underlying sums.
• Weighting by the wrong thing. Average price is weighted by units; average margin by revenue.

Performance notes

Column multiplication and sums are vectorised and run on millions of rows in milliseconds. Grouped sums followed by one division are much faster than groupby().apply with numpy.average, which calls Python once per group.

Conclusion

SUMPRODUCT becomes multiply-then-sum in pandas, with boolean masks in place of Excel's multiplied conditions and a product column plus groupby for per-group totals. A weighted average is the sum of value times weight divided by the sum of weights, computed on one consistent set of rows; per group, sum both parts first and divide after; and totals of averages are always recomputed from the underlying sums, never averaged.

Frequently asked questions

What is the pandas equivalent of SUMPRODUCT? Multiply the columns and sum the result: (df"units" * df"price").sum(). For two columns this is also the dot product, df"units".dot(df"price"), though dot does not skip missing values.

How do I compute a weighted average in pandas? Divide the SUMPRODUCT of value and weight by the sum of the weights: (df"price" * df"units").sum() / df"units".sum(). numpy.average(values, weights=weights) gives the same result when there are no missing values.

Why is the average of the regional averages different from the overall average? Because each regional average carries the weight of its own volume, and a simple mean of them treats a region with 5 units the same as one with 500. Compute the overall weighted average from the underlying sums instead.

How do missing weights affect a weighted average? A row with a missing weight or value should usually be left out of both the numerator and the denominator. Masking such rows once, before computing either sum, keeps the two consistent.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas — unweighted conditional averages.
• Calculate Percent of Total per Group in pandas — shares that also need consistent totals.
• Use Different Aggregations per Column in a pandas Pivot — sums and averages side by side.