Creating Items Purchased report using formula sample 1 pg 96

Creating 'Items Purchased' Report Using Formulas

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

Sample 1

	A			B
18	ITEM			QTY
19	Gum X			190
20	Gum Z			235
21 	Pencil 2A		180
22	Pencil 28		180
23	Total			785
24

This sample shows the total quantity of each item sold.

The formula in cell B19 is:

=SUMIF(DATA!W5:W30000, A19, DATA!Y5:Y30000)

This formula uses Excel's SUMIF function to get the total of values in column Y (in sheet DATA) where items in column W (in sheet DATA) matches (in spelling) the item in cell A19 (in the current sheet). Remember, column Y in sheet DATA contains quantities of items purchased while column W contains the names of the items purchased. (Refer to topic 29 for more information).

You should understand the use of the SUMIF function so that you can use it in cells below B19. (Refer to Topic 29 for more information).

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

The formula in cell B23 is:

=SUM(B19:B22)

This formula simply gets the total of the quantities in cells B19 to B22.

96