Creating Items Sold report using formula sample 2 pg 103 104

Sample 2

	
	A	    B		C		D		E		F		G
21
22 	ITEM/MTH		1		2		3		4		Total
23 	Model A	    QTY		249,048		203,897		282,726		49,324		784,995
24		    RM-CREDIT	(1.323,990)     (1,099,449)     (1,521,920)	(260,878)	(4,206,237)
25 	Model B	    QTY		162,610		235,906		252,330		159,215		810,061
26		    RM-CREDIT	(865,699)	(1,249,328)	(1,376,298)	(849,610)	(4,340,934)
27 	Model C	    QTY		136,341		198,838		247,709		344,895		927,783
28		    RM-CREDIT	(722,783)	(1,077,941)	(1,364,610)	(1,779,477)     (4,944,811)	
29 	Total	    QTY		547,999		638.641		782,765		553,434		2,522,839
30		    RM-CREDIT 	(2.912,472)	(3,426,718)	(4.262,827)	(2,889,964)	(13,491,982)
31

This report shows monthly total quantity and values (in RM) of each product item sold.

The formula in cell C23:
=SUMPRODUCT((DATA!X5:X30000=A23)*(DATA!C5:C30000=C22)*(DATA!Y5:Y30000))

This formula uses Excel's SUMPRODUCT function to get total of quantities in column Y (in sheet DATA) where items in column X (in sheet DATA) matches (in spelling) the item in cell A23 (in the current sheet) and items in column C (in sheet DATA) matches (in spelling) the item in cell C22 (in the current sheet). Remember, column Y in sheet DATA contains quantities of items sold, column X contains the names of items sold, and column C contain the month of transactions.

103

The formula in cell C24:
=SUMPRODUCT((DATA!X5:X30000-A23)*(DATA/C5:C30000-C22)*(DATAIJS:J30000))

This formula use Excel's SUMPRODUCT function to get the total values in column J (in sheet DATA) where items in column X (in sheet DATA) matches (in spelling) the item stated in cell A23 (in the current sheet) and items in column C (in sheet DATA) matches (in spelling) the item in cell C22 (in the current sheet). Remember, column J in sheet DATA contains RM values of items sold, column X contains the names of items sold, and column C contain the month of transactions.

Copy the formulas into other relevant cells. You must be able to understand the use of the SUMPRODUCT function to enable you to correctly construct the formulas similar to the above into other cells.

To get the details for each item/month, you can use Data/Filter/AdvanceFilter. You can read more about Filtering in Topic 13. menu

The formula in cell G23 is:
=SUM(C23:F23)

Copy this formula down until G30.

The formula in cell C29 is:
=C23+C25+C27

Copy this formula to the right until cell F29.

The formula in cell C30 is:
=C24+C26+C28

Copy this formula to the right until cell F30.




104