How to Perform a Case-Sensitive DSUM in Google Sheets

Published on

We can use the EXACT function, which finds identical strings, in conjunction with DSUM for case-sensitive DSUM calculations in Google Sheets. However, I want to clarify that the QUERY function can produce the same result, as QUERY is inherently case-sensitive.

That said, this tutorial is dedicated to those who are specifically interested in learning how to create a case-sensitive DSUM in Google Sheets.

Here is the generic formula:

=ArrayFormula(DSUM(HSTACK(data, VSTACK(,  EXACT(CHOOSEROWS(criteria_range, SEQUENCE(ROWS(criteria_range)-1, 1, 2)), criterion))), field, VSTACK("", TRUE)))

In this formula, you need to specify the following:

  • data: The dataset reference.
  • criteria_range: The reference to the criteria column, including the header.
  • field: The column label in the data containing the values to be extracted and operated on.
  • criterion: The condition used to filter the data.

Now, let’s go through an example of a case-sensitive DSUM in Google Sheets using the formula above.

Example of a Case-Sensitive DSUM

Assume you have data in the range A1:G, where A1:G1 contains the following field labels:

ABCDEFG
Product CodeReceipt DateProduct DescriptionUnitQtyUnit RateAmount

If you want to sum the values in the “Amount” column, case-sensitively matching the product code “IL102B” in column A, you can use the following formula:

  • data: A1:G
  • criteria_range: A1:A
  • field: “Amount”
  • criterion: “IL102B”

Here is the case-sensitive DSUM formula for the above data:

=ArrayFormula(DSUM(HSTACK(A1:G, VSTACK(,  EXACT(CHOOSEROWS(A1:A, SEQUENCE(ROWS(A1:A)-1, 1, 2)), "IL102B"))), "Amount", VSTACK("", TRUE)))

Formula Breakdown

Note: Following this breakdown is optional; you can skip to the next section if you're already familiar with the formula.

The regular DSUM syntax is as follows:

DSUM(database, field, criteria)

The formula follows the same structure. Here’s a breakdown of each argument:

  • database: HSTACK(A1:G, VSTACK(, EXACT(CHOOSEROWS(A1:A, SEQUENCE(ROWS(A1:A)-1, 1, 2)), "IL102B")))
  • field: "Amount" (Usually, we use the field number, but database functions also support field labels. For a case-sensitive DSUM, you must use the field label, not the field number.)
  • criteria: VSTACK("", TRUE)

The key to achieving a case-sensitive DSUM lies in the database part.

In a regular DSUM, the database would simply be A1:G. However, here we use:

HSTACK(A1:G, VSTACK(,  EXACT(CHOOSEROWS(A1:A, SEQUENCE(ROWS(A1:A)-1, 1, 2)), "IL102B")))

Note: The EXACT function in this formula operates on an array of values, which is why we need to use ARRAYFORMULA with DSUM. This ensures the function applies to the entire array.

Here’s what we’ve done in the database part:

Horizontally stacked (HSTACK) the original database A1:G with the following:

VSTACK(,  EXACT(CHOOSEROWS(A1:A, SEQUENCE(ROWS(A1:A)-1, 1, 2)), "IL102B"))

This stacked part applies the EXACT function to all values in column A using the criterion “IL102B”. Wherever the criterion matches, it returns TRUE; otherwise, FALSE.

The role of the EXACT function in case-sensitive DSUM

The CHOOSEROWS and SEQUENCE combo in this formula retrieves all the values in the criteria column (column A) except the header row. This ensures the criterion is applied only to the data values, not the header. The VSTACK then adds an empty string as the header for this stacked column.

For the criteria argument, i.e., VSTACK("", TRUE), we specified an empty field label and TRUE as the criterion.

QUERY: Alternative to Case-Sensitive DSUM in Google Sheets

As mentioned earlier, you can use the QUERY function as an alternative to the case-sensitive DSUM in Sheets.

In our example, you can replace the DSUM formula with the following QUERY formula:

=QUERY(A1:G, "SELECT SUM(G) WHERE A='IL102B'", 1)

This query selects the sum of values in column G (which could represent amounts) where the corresponding values in column A equal ‘IL102B’.

This follows the syntax:

QUERY(data, query, [headers])

Where:

  • data: A1:G
  • query: “SELECT SUM(G) WHERE A=’IL102B'”
  • headers: 1 (the number of header rows in the data, usually either 1 or 0)

Resources

Below are some helpful guides to mastering the DSUM database function in Google Sheets:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.