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:
| A | B | C | D | E | F | G |
| Product Code | Receipt Date | Product Description | Unit | Qty | Unit Rate | Amount |
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 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:
- How to Use Date Difference as Criteria in DSUM in Google Sheets
- Difference Between SUMIFS and DSUM in Google Sheets
- Comparing SUMIFS, SUMPRODUCT, and DSUM with Examples in Google Sheets
- How to Properly Use Criteria in DSUM in Google Sheets [Chart]
- AND, OR in Multiple Criteria DSUM in Google Sheets (Within Formula)
- Google Sheets: How to Use Multiple Sum Columns in DSUM Function
