Profit by Product with SUMIFS

All versionsSUMIFS

Roll a transaction log up into profit by product. SUMIFS totals revenue and cost for each product, and the difference is its profit — the foundation of any product P&L.

Quick formula: for revenue and cost columns with a product key:

=SUMIFS(revenue, product, "Widget") - SUMIFS(cost, product, "Widget")

Total revenue minus total cost for that product. Copy with the product name relative to get a per-product table.

Functions used (tap for the full reference guide):

SUMIFS SUMPRODUCT

The example

Profit for Widget from a sales log.

	A	B	C
1	Product	Revenue	Profit
2	Widget	$4,000	$1,500
3	Gadget	$6,000	$2,100

The formula

Profit per product from the log:

=SUMIFS(rev, prod, A2) - SUMIFS(cost, prod, A2) // revenue − cost, per product

How it works

Two conditional sums, then subtract:

SUMIFS(revenue, product, name) totals all revenue rows for that product.
Do the same for cost, then subtract for profit.
Put the product name in a cell and reference it so the formula fills down into a per-product table.
Add a margin % column: =profit / revenue.

Computed profit per row? If profit isn’t a column, compute it inline with SUMPRODUCT: =SUMPRODUCT((prod=A2)*(rev-cost)) totals (revenue−cost) for the product in one shot.

Try it: interactive demo

Live demo

Log lines as “product,revenue,cost”; pick a product.

Product

Revenue · Profit

Variations

Margin %

Profit over revenue:

=profit / SUMIFS(rev, prod, A2)

Inline (SUMPRODUCT)

No profit column needed:

=SUMPRODUCT((prod=A2)*(rev-cost))

Top product

Best profit:

=INDEX(prods, MATCH(MAX(profits), profits, 0))

Pitfalls & errors

Exact product keys. SUMIFS matches text exactly (case-insensitive) — trailing spaces or typos split a product into two.

Align the ranges. The criteria range and sum range must be the same size and orientation.

Profit needs both sides. Make sure every revenue row has a matching cost basis, or profit is overstated.

Practice workbook

📊

Download the free Profit by Product with SUMIFS practice workbook

A product-profit sheet with SUMIFS, the margin-%, SUMPRODUCT, and top-product variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate profit by product in Excel?

Subtract conditional sums: =SUMIFS(revenue, product, name) - SUMIFS(cost, product, name) gives each product’s profit from a transaction log.

How do I get profit without a profit column?

Use SUMPRODUCT: =SUMPRODUCT((product=name)*(revenue-cost)) totals revenue minus cost for that product inline.

How do I add a margin percentage?

Divide profit by revenue: =profit / SUMIFS(revenue, product, name).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUMIFS · SUMPRODUCT