How to sum data if it falls under any arbitrary number of date ranges

Question

I need a single formula for summing numbers that fall within an arbitrary number of date ranges.

You can use multiple SUMIFS() for each date range and then sum the result of those SUMIFS()s within a single cell like this:

=SUMIFS(dataRange, datesRange,">="&startDate, D:D,"<="&endDate) + 
 SUMIFS(dataRange, datesRange,">="&startDate, D:D,"<="&endDate) + 
 SUMIFS(dataRange, datesRange,">="&startDate, D:D,"<="&endDate)

But this requires that I know exactly how many date ranges I'm going to have. For my spreadsheet I won't know how many date ranges I will have, so it has to dynamically grab the dates ranges.

Sample Google Spreadsheet (currently editable, please feel free to use)

I was playing around with your spreadsheet and I created a named range named AllDates G4:G. Then I used that named for creating validation for your dateranges and now you can pick the date ranges much easier I think. You can always remove it if you don't like it. — Cooper

curious curious · Accepted Answer · 2017-08-05T02:27:35

You can use SUMIF in conjunction with the SUMPRODUCT function that multiplies components in the given arrays, and returns the sum of those products.

This can be done as below:

=SUMPRODUCT(SUMIFS(E:E,D:D,">="&A4:A100,D:D,"<="&B4:B100))

How to sum data if it falls under any arbitrary number of date ranges

1 Answers