CODEWITHSUNDEEP
dategoogle-sheetssumifs
benlimpich
benlimpich

Reputation: 1

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

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)

Upvotes: 0

Views: 144

Answers (1)

curious
curious

Reputation: 1498

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))

Upvotes: 0

Related Questions