Sumproduct

[Papi]

The workbook below needs to do a couple of things. I need it to count the number of items for a specific month based on one item per row i.e. the Quantity (No.) for Manifold base in August would be 2 and then the total cost for the same month. I'm not sure how to change the month as the list will continue on and each month I will have to identify the month and then collect the data by month i.e July would simply be 1 Boat Pin at $56.53 and 1 Anchor at $75.00 but August would have 2 Envelope Cases for a total of $10.56, 2 Manifold bases for a total of $130.60 and 1 Drill at $204.16 etc.

Monthly Parts List.xls
	A	B	C	D
1		Jul1,2011	No.	Total
2		Anchor
3		Boat
4		Cart
5		Drill
6		EnvelopeCase
7		Manifoldbase
8
9	Day	PartDescription	Unit	Cost
10	7/25/2011	BoatPin		56.53
11	7/31/2011	Anchor		75.00
12	8/5/2011	EnvelopeCase		5.28
13	8/8/2011	ManifoldBase		65.30
14	8/9/2011	Drill		204.16
15	8/12/2011	ManifoldBase		65.30
16	8/15/2011	EnvelopeCase		5.28
sheet2

 

[Aladin Akyurek]

The workbook below needs to do a couple of things. I need it to count the number of items for a specific month based on one item per row i.e. the Quantity (No.) for Manifold base in August would be 2 and then the total cost for the same month. I'm not sure how to change the month as the list will continue on and each month I will have to identify the month and then collect the data by month i.e July would simply be 1 Boat Pin at $56.53 and 1 Anchor at $75.00 but August would have 2 Envelope Cases for a total of $10.56, 2 Manifold bases for a total of $130.60 and 1 Drill at $204.16 etc.

Monthly Parts List.xls
	A	B	C	D
1		Jul1,2011	No.	Total
2		Anchor
3		Boat
4		Cart
5		Drill
6		EnvelopeCase
7		Manifoldbase
8
9	Day	PartDescription	Unit	Cost
10	7/25/2011	BoatPin		56.53
11	7/31/2011	Anchor		75.00
12	8/5/2011	EnvelopeCase		5.28
13	8/8/2011	ManifoldBase		65.30
14	8/9/2011	Drill		204.16
15	8/12/2011	ManifoldBase		65.30
16	8/15/2011	EnvelopeCase		5.28
sheet2

Define Lrow as referring to:

=MATCH(9.99999999999999E+307,Sheet2!$A:$A)

Define Dates as referring to:

=Sheet2!$A$10:INDEX(Sheet2!$A:$A,Lrow)

Parts as referring to:

=Sheet2!$B$10:INDEX(Sheet2!$B:$B,Lrow)

Costs as referring to:

=Sheet2!$D$10:INDEX(Sheet2!$D:$D,Lrow)

Assuming that B1 is a true date...

C2, just enter and copy down:

=SUMPRODUCT(--(Dates-DAY(Dates)+1=$B$1),--(Parts=$B2))

D2, just enter and copy down:

=SUMPRODUCT(Costs,--(Dates-DAY(Dates)+1=$B$1),--(Parts=$B2))

 

[Papi]

Thanks very much Aladin. I will have to look at your data later on at work as I am not clear about what the first eight rows of information mean and what I am supposed to do with them. Hopefully it will make sense once my nose is to the grindstone.

 

[Aladin Akyurek]

Thanks very much Aladin. I will have to look at your data later on at work as I am not clear about what the first eight rows of information mean and what I am supposed to do with them. Hopefully it will make sense once my nose is to the grindstone.

The ranges of interest the formulas have to refer to are defined as dynamic named ranges by the recipe given in those rows. This means that you don't have to adjust the ranges manually when they grow or shrink.

 

[Papi]

Hello Aladin,

This is great! The only thing I have not figured into the mix is what the first item does and how?

"Define Lrow as referring to: =MATCH(9.99999999999999E+307,Sheet2!$A:$A)

Also, I put a macro in place to move the date ahead (or back) by one month at a time and it changes your numbers accordingly. One additional question - Is it possible to have the Date range filter to match the month in cell B1 by filtering A9 to match the same month?

 

[Aladin Akyurek]

Hello Aladin,

This is great! The only thing I have not figured into the mix is what the first item does and how?

"Define Lrow as referring to: =MATCH(9.99999999999999E+307,Sheet2!$A:$A)

Lrow (from last row) determines at any moment the position of the last data record by looking at the numeric data in column A on Sheet2. This number is used in order to set up dynamic named ranges the SumProduct formulas refer to. This way you don't need to manually adjust the ranges whenever any change occurs in the data.

See for more:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)

Also, I put a macro in place to move the date ahead (or back) by one month at a time and it changes your numbers accordingly.

Good idea...

One additional question - Is it possible to have the Date range filter to match the month in cell B1 by filtering A9 to match the same month?

I would think that the summary calculations should suffice. If not, you can use perhaps another macro.

 

[Papi]

Excellent. Thanks for all your help Aladin. I'll do some more reading. What you have done has this workbook performing at a whole new level. Again, thank you.

 

[Aladin Akyurek]

Excellent. Thanks for all your help Aladin. I'll do some more reading. What you have done has this workbook performing at a whole new level. Again, thank you.

You are welcome. Thanks for providing feedback.