Sum Product/ Sum Ifs I Don't Know - Multiple Criteria Summation

[DHAMMOND]

Hello,

I need help summing a range based on criteria. I've tried sumifs and sumproducts, but I can't get it right. The goal is to sum a range based on: Retailer, Brand, and the time period between two dates. The formula will go into cell B10 and reference a table on a different worksheet.

Could someone please help me out with a correct formula?


Sheet 1 (Summary Sheet)

1	A	B	C	D	E	F	G	H
2	Criteria
3	Retailer	frank
4	sales start	7/31/2017
5	sales end	8/14/2017
6
7
8	sales summary base on criteria
9	Brand	Sales
10	Red	?
11	Green
12	Blue

<tbody>
</tbody>

Sheet 2 (data sheet) - sales data in a3:d9

1				A	B	C	D
2				7/31/2017 <tbody> </tbody>	8/7/2017 <tbody> </tbody>	8/14/2017 <tbody> </tbody>	8/21/2017 <tbody> </tbody>
3	red <tbody> </tbody>	Frank <tbody> </tbody>
4	red	Jill <tbody> </tbody>
5	blue	Tom <tbody> </tbody>
6	green	Jill <tbody> </tbody>
7	blue	Tom <tbody> </tbody>
8	green	Frank <tbody> </tbody>
9	red	Tom <tbody> </tbody>
10
11
12

<tbody>
</tbody>

7/31/2017 <tbody> </tbody>

<tbody>
</tbody>

 

[Oscar Cronquist]

The column labels seem to wrong on sheet2? Column A is above red?


Formula in cell B10:


=SUMPRODUCT((A10=Sheet2!A3:A9)*(B3=Sheet2!B3:B9)*(Sheet2!C2:F2<=B5)*(Sheet2!C2:F2>=B4)*Sheet2!C3:F9)

 

[DHAMMOND]

Brilliant! Thank you!