Index / Match with Sum

[volkl77]

Just wondering if there is any way that i can use sum with index/match to return the sum of values meeting multiple criteria? I have a sheet with a row of dates across the top, and a column of categories down the side as well as a separate summary sheet. I would like to have my summary sheet add the values for a category based on a specified range of dates. So, the total for category "X" for the dates April 4-10. Thanks!

 

[Ron Coderre]

You could use something like this:
A1: Category
A2: StartDate
A3: EndDate

=SUMPRODUCT((date_range>=A2)*(date_range<=A3)*(category_range=A1)*values_range)

Does that help?

 

[T. Valko]

Just wondering if there is any way that i can use sum with index/match to return the sum of values meeting multiple criteria? I have a sheet with a row of dates across the top, and a column of categories down the side as well as a separate summary sheet. I would like to have my summary sheet add the values for a category based on a specified range of dates. So, the total for category "X" for the dates April 4-10. Thanks!

Dates in the range B1:J1
Categories in the range A2:A4
Numeric data in the range B2:J4

Use cells to hold the sum criteria:

• A10 = some category
• B10 = lower date boundary
• C10 = upper date boundary

Then:

=SUMPRODUCT((A2:A4=A10)*(B1:J1>=B10)*(B1:J1<=C10)*B2:J4)