Creating Items Sold report using formula sample 1 pg 102 103

Creating 'Items Sold' Report Using Formulas

We can build an Items Sold report either by using formulas or by using a PivotTable operation.

Sample 1

	A			B			C
15
16	ITEM		QTY			RM
17 	Model A		784,995		(4,206,237)
18 	Model B		810,061		(4,340,934)
19 	Model C		927,783		(4,944,811)
20 	Total		2,522,839	(13,491,982)
21

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

The formula in cell B17:

=SUMIF(DATA!X5:X30000, A17, DATA!Y5:Y30000)

This formula uses Excel's SUMIF 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 A17 (in the current sheet). Remember, column Y in sheet DATA contains quantities of items sold while column X contains the names of items sold.

The formula in cell C17:

=SUMIF(DATA!X5:X30000, A17, DATA!J5:J30000)

This formula also use Excel's SUMIF function to get total values (in RM) of quantities sold in column J (in sheet DATA) where items in column X (in sheet DATA) matches (in spelling) the item in cell A17 (in the current sheet). Remember, column J in sheet DATA contains RM values of items sold while column X contains the names of items sold.

You must understand the use of SUMIF function so that you can use it in cells below B17 and C17. (Refer to topic 29).

102

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

The formula in cell B20 is:
=SUM(B17:B19)

The formula in cell C20 is:
=SUM(C17:C19)