I’ve got a juicy secret for you when it comes to using DSUM in Google Sheets. In my comprehensive tutorial, I covered everything you need to know about this powerful function. But today, I want to dive deeper and show you how to use date difference as a criterion in DSUM.
Before we get started, let me just clarify one thing. When using functions like QUERY in Google Sheets, you need to pay attention to the format of your dates. But with DSUM, things are a bit different. So, buckle up and let’s get started!
Date or Date Range Conditions in DSUM
When using date criteria in any function (except QUERY), it’s important to avoid entering them as text. This helps prevent errors that can arise from variations in date formatting, such as DD/MM/YYYY or MM/DD/YYYY. And guess what? DSUM is no exception to this rule!
So, how do you properly use date criteria in DSUM? Well, it’s simple! To filter a table based on a specific date and sum the values, you need to enter the date criterion in the following syntax: DATE(yyyy, mm, dd).
And what about comparison operators? Here’s the syntax: ">"&DATE(yyyy, dd, mm). Feel free to modify the comparison operators based on your specific requirements. You can use “>”, “>=”, “<“, or “<=”.
Now, let’s dive into a practical example to see how date difference criteria work in DSUM.
How to Use Date Difference as Criteria in DSUM – Example
Imagine we have a sample dataset in the range A1:B6, where A1:B1 contains field labels. Yes, DSUM requires structured data with field labels. Just data ranges without field labels won’t work.
Here’s an example that will walk you through the process step by step.
Criterion without Comparison Operator in DSUM
Let’s say you want to sum the values in column B if the date in column A is 30/08/2017. Follow these steps:
- Enter the field label “Sales Date” in cell C3.
- In cell C4, enter the date criterion for DSUM as
=DATE(2017, 8, 30). - Use the following formula in cell D4 to get the sum:
=DSUM(A1:B6, 2, C3:C4).
Here’s a shortcut version of the formula that uses curly braces or VSTACK:
- Curly Braces:
=DSUM(A1:B6, 2, {"Sales Date"; DATE(2017, 8, 30)}) - VSTACK:
=DSUM(A1:B6, 2, VSTACK("Sales Date", DATE(2017, 8, 30)))
Criterion with Comparison Operator in DSUM
Now, let’s say you want to sum the values in column B if the date in column A is greater than 03/07/2017.
- In cell C4, enter the criteria with the comparison operator:
=">"&DATE(2017, 7, 3). - If you prefer to see the date instead of the date value, use the JOIN function to combine the comparison operator with the date, like this:
=JOIN("", ">=", DATE(2017, 7, 3)). - Use the following formula in cell D4 to get the sum:
=DSUM(A1:B6, 2, C3:C4).
And of course, we have the shortcut versions:
- Curly Braces:
=DSUM(A1:B6, 2, {"Sales Date"; ">="&DATE(2017, 7, 3)}) - VSTACK:
=DSUM(A1:B6, 2, VSTACK("Sales Date", ">="&DATE(2017, 7, 3)))
DSUM with Is Between Two Dates
If you want to go even further and use multiple comparison operators in DSUM, I’ve got you covered. Let’s say you want to find the total sales during the period 01/07/2017 to 31/07/2017.
- Enter the field labels “Sales Date” in cells C3 and D3.
- In cell C4, enter
">="&DATE(2017, 7, 1). - In cell D4, enter
"<="&DATE(2017, 7, 31). - Use the following formula to get the sum:
=DSUM(A1:B6, 2, C3:D4).
Here are the shortcut versions:
- Curly Braces:
=DSUM(A1:B6, 2, {{"Sales Date"; ">="&DATE(2017, 7, 1)}, {"Sales Date"; "<="&DATE(2017, 7, 31)}}) - VSTACK:
=DSUM(A1:B6, 2, HSTACK(VSTACK("Sales Date", ">="&DATE(2017, 7, 1)), VSTACK("Sales Date", "<="&DATE(2017, 7, 31))))
So, there you have it! You’ve just unlocked the secret to using date difference as criteria in DSUM in Google Sheets. But before you go, let me share some additional resources that will help you become a DSUM expert:
- How to Do a Case-Sensitive DSUM in Google Sheets
- AND, OR in Multiple Criteria DSUM in Google Sheets (Within Formula)
- Google Sheets: How to Use Multiple Sum Columns in DSUM Function
These tutorials will take your DSUM skills to the next level. So, grab a cup of coffee and dive into the wonderful world of DSUM!
Remember, this secret was brought to you by Crawlan.com, where you’ll find all the tips and tricks you need to become a Google Sheets pro. Happy sheeting!
