HomeGoogle DocsSpreadsheetHow to Use Date Differences as Criteria in SUMPRODUCT in Google Sheets

How to Use Date Differences as Criteria in SUMPRODUCT in Google Sheets

Published on

Date difference as criteria refers to the duration between dates or dates falling between two given dates. In this Google Sheets tutorial, I will guide you on how to utilize date differences as criteria in the SUMPRODUCT function.

Assume you want to use SUMPRODUCT for conditional sum (using a date criteria column and an amount column). What about functions like SUMIFS and DSUM (conditional sum functions) with the same type of criteria?

The usage of date criteria in DSUM is almost identical to the usage of date criteria in SUMIFS. If you are new to this as well, I have already provided detailed tutorials.

However, the usage of date criteria in SUMPRODUCT is slightly different. I will explain it below with an example.

The date field as a criterion in the SUMPRODUCT function is one of the areas where you should pay attention. When dealing with multiple criteria in SUMPRODUCT, you may falter otherwise.

Using Date Differences as Criteria in SUMPRODUCT in Google Sheets

In this illustration, I am summing the “Sales Qty.” for sales that occurred between 01/07/2017 and 31/07/2017.

Observe how SUMPRODUCT is used with date criteria in Google Sheets.

Here is the formula for this somewhat uncommon SUMPRODUCT date criteria. Typically, Google Sheets users prefer SUMIFS or DSUM in such scenarios.

=SUMPRODUCT((D2:D11>=DATE(2017, 7, 1))*(D2:D11<=DATE(2017, 7, 31))*(E2:E11))

Note: In the formula, we have specified the date using the syntax DATE(year, month, day)

Image: Date Differences as Criteria in SUMPRODUCT in Google Sheets

In addition to the date criteria in SUMPRODUCT, there’s another noteworthy element in this formula—the use of multiple criteria in the same field. Pay attention to the brackets used in the formula.

In the above formula that employs date difference as criteria in SUMPRODUCT, I’ve utilized the same “Date” field, i.e., D2:D11, twice.

With the above example, you can grasp the following points:

When using multiple criteria in the same field, employ the formula as follows (only the date part is shown):

(D2:D11>=DATE(2017, 7, 1))*(D2:D11<=DATE(2017, 7, 31))

When using a single criterion in the same field, use the formula as follows:

D2:D11=DATE(2017, 7, 1)

These formulas (criteria test) return an array of values with 1 (TRUE) or 0 (FALSE). Multiplying them with the ‘Qty’ column and summing them equals the total quantity, and that is what the SUMPRODUCT does.

Conclusion

I hope you found answers to the following queries in the tutorial:

  • How to use date differences as criteria in SUMPRODUCT in Google Sheets?
  • How to use multiple criteria in the same field or array in Google Sheets?
  • Finally, how to use multiple criteria in SUMPRODUCT?

The SUMPRODUCT function is easy to learn and use. Many people encounter errors when using functions like SUMPRODUCT, SUMIFS, or DSUM due to variations in criteria.

To overcome this, my suggestion is to stick with the function that suits you best and is easy to learn.

Resources:

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here