DPRODUCT in Excel: Multiply Selected Values and Preserve the Factor Model

=DPRODUCT(database, field, criteria)

The database range includes the header row. Field identifies the column to calculate: use a quoted heading such as “Units”, or its one-based position within the database. Here Units is field 3, even if the database is later moved to another part of the sheet. Criteria is a separate range containing matching field headers and at least one conditions row. All three arguments are required.

Enter this numeric database in A1:D7:

Put Team in F1 and Product in G1. In F2 enter ="=North"; in G2 enter ="=A". These formulas produce the exact-match criteria text =North and =A. The range F1:G2 selects the three North/A records, whose Units values are 2, 4 and 6 and Sales values are 100, 200 and 300.

Conditions in the same criteria row act together with AND. Alternative criteria rows act with OR. Keep the criteria and output cells outside the database, and leave room for the data to grow.

=DPRODUCT(A1:D7,"Units",F1:G2)
=DPRODUCT(A1:D7,3,F1:G2)

Both have expected value 48, from 2×4×6. The corresponding sum is 12. A product is a different aggregation and should not be labeled as total Units.

The Units fixture makes the arithmetic easy to audit, but multiplying independent sales quantities is not automatically a meaningful business metric. The field’s interpretation must justify a multiplicative model.

Put Team in J1 and ="=North" in J2. DPRODUCT on Units with J1:J2 selects 2, 9, 4 and 6, giving 432. The additional factor 9 multiplies the former product by nine.

A product can grow rapidly as records are included. A copied or duplicated factor has a multiplicative effect, so record uniqueness and criteria scope are especially important.

Create a separate table in A12:B15 with headers Scenario and Factor. Its rows are Base/1.02, Base/1.03 and Base/0.98. Put Scenario in F12 and ="=Base" in F13.

=DPRODUCT(A12:B15,"Factor",F12:F13)

Expected combined factor: 1.029588. Subtract one to express the net proportional change, 0.029588, or 2.9588%. Applied to an initial amount of 100, the ending modeled amount is 102.9588.

The entries are growth factors 1+rate, not the raw rates 2%, 3% and -2%. Multiplying the raw rates would answer a different question. This example assumes the factors describe sequential multiplicative changes and does not forecast any real return.

For a separate numeric factor set 2, 4 and 0, the mathematical product is 0. For -2, 4 and 6 it is -48; for -2, -4 and 6 it is 48. The count of negative factors determines the sign when none is zero.

Duplicating the full original set gives 2×4×6×2×4×6=2304, the square of 48. A duplicated database block can therefore materially alter a result even though no new distinct values appear.

For strictly positive factors 1.02, 1.03 and 0.98, =EXP(LN(1.02)+LN(1.03)+LN(0.98)) has the same mathematical target 1.029588. The logarithms convert multiplication into addition.

This control does not apply directly to zero or negative factors in real-valued logarithms. It can avoid some large intermediate products, but the final result and floating-point precision still have limits.

The dedicated DPRODUCT reference does not establish every no-match, no-numeric or mixed-error result. Do not assume the mathematical empty-product convention is necessarily the native output for all empty database cases. Check selection counts and inspect target cells before replacing errors or unexpected zeros.

The worked examples are numeric. Text coercion, physically empty fields and formula errors remain outside their verified scope.

Use the complete header-and-condition range, not just the condition cells. Check the field heading for spelling and spacing, and verify that a numeric field index still points to the intended column after a layout change. A worksheet’s visible rows are not a substitute for the explicit database range and criteria used in the formula.

Do not include an accidental empty conditions row in the criteria range: an unrestricted alternative can broaden selection. Microsoft’s DAVERAGE reference documents the shared AND/OR, exact-text and case-insensitive matching rules. These examples avoid wildcard, case-sensitive-formula and numeric-text complications.

The database functions do not validate whether the selected records are the right records for the question. Keep a matching-record or numeric-field count beside an important result. Native blank/formula-empty handling, coercion, extreme precision and error propagation not explicitly documented in this guide remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft DPRODUCT reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.