How To Use DSUM & MATCH Functions With Structured Referencing & Data Validation To Create Dynamic Calculation

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to nest match function inside rarely known DSUM database function coupled with Tables Structured Referencing & List Data Validation to create a dynamic Total Sales. Let’s get started.

Before we delve into the formula, let us consider what is DSUM and MATCH functions and their syntax

• DSUM adds the numeric values in column of records in the database that match the specified criteria
• DSUM Syntax                     =DSUM(database, field, criteria)
• MATCH returns the relative position of a value in an array that matches a specified value in a specified order
• MATCH Syntax                   =MATCH(lookup_value, lookup_array, [match_type])

The first thing we wants to do is to set-up our first List Data Validation in cell D4 for Account Manager. To do that:

• In cell D4, click on Data Validation located in the Data Tools group of the Data tab
• In the Data Validation dialogue box, select List from the drop-down in the Settings tab
• In the Source, select all the names located in column H

• Click OK.

To create the second data validation for Product Category:

• In cell E4, click on Data Validation located in the Data Tools group of the Data tab
• In the Data Validation dialogue box, select List from the drop-down in the Settings tab
• In the Source, select all the names located in column I

• Click OK. Note, the data is already stored in Excel Table with the names Data & criteria respectively

In cell F4, executive the formula: =DSUM(data[#All],MATCH(“Total”,data[#Headers],0),criteria[[#All],[Account Manager]:[Product Category]]) and click Enter

The Total Sales returned ₦1,221,473.52 which tallied to the same result in the Status Bar as seen below

To know the Total Sales generated by Account Manager named Natasha Love for Product Category that equals Beverages:

• Select Natasha Love and Beverages from the list dropdown in cell D4 and E4 respectively. Her Total Sales generated in ₦21,209.50

To verify the ₦21,209.50 value, we will filter the data with the same criteria above. See the result below

That is how to deploy DSUM and MATCH functions coupled with Structured Referencing and Data Validation to create dynamic formula