Need help

[j_sun]

Hi,

I'm stuck. I've been given a challenge and I am stumped. I've basically got a table with product names in one column (only 7 unique product names. they do not reoccur)and the number of that product sold in individual columns representing one day each. So Column A is product, Column B is 1/1/10, Column C is 1/2/10.....Column I is 1/7/10.

There are 3 parameter cells that you enter data in "Product Name", "Start Date" and "End Date"

Based on the selections in those parameter cells I need to sum the total number of that product sold between the dates in the start and end range.


How do I do this? I'm think a combo of INDEX and/or OFFSET but I can't tie it all together.


Thanks,

Jason

 

[j_sun]

Ugghh. I should have know better. Can't delete this so disregard.

Here is the table:

[pre]

Name		1/1/10	1/2/10	1/3/10	1/4/10	1/5/10	1/6/10	1/7/10

Strawberry		32  	10  	10  	125  	45  	58  	30
Blackberry		42  	23  	3  	14  	32  	4  	81
Blueberry		6  	34  	12  	18  	2  	5  	30
Huckleberry		8  	8  	76  	2  	153  	4  	30
Mulberry		456  	5  	23  	33  	14  	23  	37
Cranberry		14  	334  	5  	22  	8  	3  	41
Wilkesberry		1  	3  	52  	3  	94  	91  	4  

Name	Start Date	End Date						

Wilkesberry	1/2/10	1/6/10

[/pre]

 

[Luke M]

You might already have this figured out, but something like:

=SUMPRODUCT((A2:A10=Name)*(B1:G1>=StartDate)*(B1:G1<=EndDate)*(B2:G10))

 

[j_sun]

That's it Luke. Thanks!

The other part of the challenge is to find the Average of those same values. Is there an equivalent AVGPRODUCT function?