How to Do a Case Sensitive DSUM in Google Sheets

0
65
How to Do a Case Sensitive DSUM in Google Sheets

It’s possible to do a Case Sensitive DSUM in Google Sheets. But at the same time I wish to make it clear that you can use Query function to get the same result as Query is case sensitive. Still I dedicate this tutorial for those who wish to know how to do a case sensitive DSUM in Google Sheets. The formula seems little complex but nothing to worry, you can catch it.

Here in this tutorial I am following the same method which I’ve adopted earlier in case sensitive SUMIF, SUMPRODUCT and COUNTIF formulas. So let’s begin.

How to Do a Case Sensitive DSUM in Google Sheets

The essence of every tutorial is a well oriented data and especially for DSUM as it’s a database function.

sample data to perform case sensitive dsum in google doc

Let me start with a normal case insensitive DSUM formula for the above database.

=dsum(A3:G8,7,A1:A2)
or
=dsum(A3:G8,“Amount”,A1:A2)

The criterion here is “IL102B”. But this DSUM formula would sum Column G, i.e. field number 7, where the criterion meet “IL102B” irrespective of its case. That means it also sums the value for “”IL102b”.

Below you can see my Case Sensitive DSUM Formula for the above example in Google Sheets. I’ve tried to compare this with the above normal DSUM formula. See the colouring.

=dsum(ArrayFormula({A3:G3;{ArrayFormula((find(A2,A4:A8))),B4:G8}}),7,{A1;1})

Formula Explanation:

The blue part of the above formula is actually acts as a virtual database. To explain what this formula does, I’ve just entered it in cell N2. See the below screenshot where I’ve marked what the combination of formulas do here.

Virtual Database in Google Sheets

virtual database in Google Sheets for DSUM Case Sensitive

The formula in Cell N2 returns a virtual database in N2:T7. See the colouring to understand the formula and what it returns. Here I’ve embraced Curly Braces to make an array with different data ranges. I think only the green coloured part requires little explanation. As a side note, if you think this’s tough to follow, I’ve shared the link of my Google Spreadsheet at the end of this tutorial. Check that.

The green coloured part is a FIND formula in an array form. The FIND is a case sensitive function which returns 1 if it finds matches and else a “#VALUE!” error. See that formula once again below.

=ArrayFormula((find(A2,A4:A8)))

In Cell A2 we have our criteria “IL102B”. This FIND array formula checks this text value in the range A4:A8 and returns an array as above (green coloured part in the virtual database screenshot). That’s it.

Now we have a virtual database. In this, we can apply a DSUM formula as usual. That’s what our original CASE SENSITIVE DSUM formula does. You can scroll up and see the original formula just above the title “Formula Explanation”.

In that DSUM, instead of criteria range A1:A2, I have used the following criteria.

{A1;1}

This is because, in the first column in our virtual database, instead of the criterion “IL102B”, now it’s the value 1.

So the above criteria would do the job of our original criteria range A1:A2. It simply replaces the value in A2, i.e. “IL102B”, with 1.

If you have any doubt, you can open my Google Sheet below and make a copy of it.

Sample Sheet

Spend time in your leisure to learn this combination so you can follow this approach to make any case insensitive formula in Google Sheets Case Sensitive.

LEAVE A REPLY

Please enter your comment!
Please enter your name here