Creating Items Purchased report using formula sample 2 pg 97

Sample 2

	A			B		C		D		E		F
24
25 	ITEM/MTH	1		2		3		4		Total
26 	Gum X		35		55		50		50		190
27	Gum Z		25		65		25		120		235
28 	Pencil 2A	55		35		55		35		180
29 	Pencil 28	65		25		50		40		180
30	Total		180		180		180		245		785
31	

This sample shows the monthly total quantity of each item sold.

The formula in cell B26 is:

=SUMPRODUCT((DATA!M5:W30000=A26)*(DATA C5:C30000-B25)*(DATA!Y5:Y30000))

This formula uses Excel's SUMPRODUCT function to get the total of the quantities in column Y (in sheet DATA) where items in column W (in sheet DATA) matches (in spelling) the item stated in cell A26 (in the current sheet) and items in column C (in sheet DATA) matches (in spelling) the item stated in cell B25 (in the current sheet). Remember, column Y in sheet DATA contains quantities of items purchased, column W contains the names of items purchased, and column C contains the months of transactions).

The other cells contain similar formulas.

You must be able to understand the use of the SUMPRODUCT function to enable you to correctly construct or copy the above formulas into other cells. (Refer to topic 29 for more information on functions).

To get the details for each item, you can use menu Data/Filter/AdvanceFilter.

The formula in cell F26 is:

=SUM(B26:E22)

The formula in cell B30 is:

=SUM(B26:B29).

97