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

0
110
multiple criterion in Sumproduct and date difference

Date difference as criteria means the dates fallen between two given dates. In this new Google Sheets tutorial I will tell you how to use date difference as criteria in SUMPRODUCT function. What about other similar functions like SUMIFS and DSUM, are they same?

Using date criteria in DSUM is almost match with the usage of date criteria in SUMIFS. If you are not learned this trick, I have already detailed tutorials on this.

DSUM: How to Use Date Difference As Criteria in DSUM in Google Sheets

SUMIFS: How to Include Same Field Twice in SUMIFS Function in Google Sheets. This Google Sheets tutorial also covers the issue.

But date criteria in SUMPRODUCT is slightly different. I will explain it below with an example. Also using date field as criteria in SUMPRODUCT function is one of the area where you should give your attention. When you are dealing with multiple criterion in SUMPRODUCT, you may falter otherwise.

multiple criterion in Sumproduct and date difference

Date Difference as Criteria in SUMPRODUCT in Google Sheets – How to

In this example we are going to sum “Sales Qty.” for the sales taken place between 01/07/2017 and 31/07/2017. This example will clearly tell you how SUMPRODUCT using date criteria in Google Sheets.

Date Difference as Criteria in SUMPRODUCT

Below is the rare SUMPRODUCT date criteria formula.¬†Normally people tend to use SUMIFS or DSUM instead of SUMPRODUCT in such situations. That’s why I like to call it ‘rare’.

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

Here we use the SUMPRODUCT multiple criterion feature in same field. You should pay attention to the brackets used, other wise you may end up in error!

In the above, to use date difference as criteria in SUMPRODUCT, we used the same “Date” field twice, i.e. “D2:D11”.

Date Criterion in SUMPRODUCT should be used as;

  1. When you want to use multiple criteria in the same field use the formula as below – only date part shown.

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

2. When you want to use single criterion in the same field use the formula as follows.

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

Note: In the above piece of formula, I used the column ranges (array) and date from our sample data. This’s for explanation purpose only.

Conclusion

Hope you understand the below three things from the above tutorial.

  1. How to use date difference as criteria in SUMPRODUCT in Google Sheets?
  2. How to use multiple criterion in the same field or array in Google Sheets.?
  3. Finally how to use multiple criterion in SUMPRODUCT?

SUMPRODUCT function is easy to learn and to use. Most of the people make errors while using functions like SUMPRODUCT, SUMIFS or DSUM. The reason, we can use any of these formulas as an alternative to the other while the syntax and usage is difference. To overcome this my suggestion is to stick with the one function that suitable to you or you find easy to learn.

Thanks for the stay. Keep visiting for more such tutorials.

LEAVE A REPLY

Please enter your comment!
Please enter your name here