Using sumproduct for specific dates

[donjr]

Hello, I've done some searches and cannot find a thread to help me.

I'm trying to calculate average commission in column L (=SUMPRODUCT(B3:B17,E3:E17)/SUM(B3:B17))
But have it only use the dates between column I and J. I need advice on to create a statement that says SUMPRODUCT IF the data in column B and E falls withing the dates in columns I and J.

Any and all help is appreciated.

A B C D E F G H I J K L M Date Qty Stock Price Commission $ Totals Start End Qty Avg Commission $ 7/2/14 15,000 X 24.565 0.01 $150 Jul-14 7/2/14 7/31/14 202,000 0.01 $2,620 7/3/14 5,500 X 24.565 0.01 $55 Aug-14 8/1/14 8/31/14 821,385 $0 7/10/14 25,000 X 24.565 0.01 $250 7/23/14 20,000 X 24.565 0.01 $200 7/23/14 20,000 X 24.565 0.01 $200 7/24/14 10,000 X 24.565 0.01 $100 7/25/14 10,000 X 24.565 0.01 $100 7/25/14 5,000 X 24.565 0.01 $50 7/25/14 5,000 X 24.565 0.01 $50 7/25/14 5,000 X 24.565 0.01 $50 7/28/14 6,500 X 24.565 0.01 $65 7/30/14 15,000 X 24.565 0.01 $150 7/30/14 30,000 X 24.565 0.02 $600 7/31/14 20,000 X 24.565 0.02 $400 7/31/14 10,000 X 24.565 0.02 $200 8/1/14 8,400 X 24.565 0.02 $168 8/1/14 26,500 X 24.565 0.02 $530 8/4/14 20,000 X 24.565 0.02 $400 8/4/14 1,500 X 24.565 0.02 $30 <tbody> </tbody>

<tbody>
</tbody>

 

[bbott]

Try this in L2 and copy down:

=SUMPRODUCT($B$2:$B$20,$E$2:$E$20,--($A$2:$A$20>=I2),--($A$2:$A$20<=J2))/SUMPRODUCT(($B$2:$B$20),--($A$2:$A$20>=I2),--($A$2:$A$20<=J2))

 

[Oeldere]

with an pivot table.

Date Qty Stock Price Commission $ value month
07-02-2014 15 X 24.565 0.01 $150 150 juli
07-03-2014 5,5 X 24.565 0.01 $55 55 juli
07-10-2014 25 X 24.565 0.01 $250 250 juli
7/23/14 20 X 24.565 0.01 $200 200 juli
7/23/14 20 X 24.565 0.01 $200 200 juli
7/24/14 10 X 24.565 0.01 $100 100 juli
7/25/14 10 X 24.565 0.01 $100 100 juli
7/25/14 5 X 24.565 0.01 $50 50 juli
7/25/14 5 X 24.565 0.01 $50 50 juli
7/25/14 5 X 24.565 0.01 $50 50 juli
7/28/14 6,5 X 24.565 0.01 $65 65 juli
7/30/14 15 X 24.565 0.01 $150 150 juli
7/30/14 30 X 24.565 0.02 $600 600 juli
7/31/14 20 X 24.565 0.02 $400 400 juli
7/31/14 10 X 24.565 0.02 $200 200 juli
08-01-2014 8,4 X 24.565 0.02 $168 168 aug
08-01-2014 26,5 X 24.565 0.02 $530 530 aug
08-04-2014 20 X 24.565 0.02 $400 400 aug
08-04-2014 1,5 X 24.565 0.02 $30 30 aug







month Gegevens Totaal
aug Som van value 1.128,00
Som van Qty 56,4
juli Som van value 2.620,00
Som van Qty 202
Eindtotaal Som van value 3.748,00
Eindtotaal Som van Qty 258,4

 

[donjr]

Thank you

 

[Oeldere]

Thanks for the reply.

Glad I could help.