## How to return the total for orders with more than one item in excel

*Article last updated on December 02, 2010*

**Question:**

I have given up trying to figure out the following problem as it seems over my head, and am

hoping there is a kind soul out there can help me out with the solution.

I am trying to setup a spreadsheet to make calculations which are then exported to Quickbooks.

The original data is returned to Excel from an Access database by msq. Each record relates to an order number and is tied to a

product, and there may be more than entry for each order number as an order may have more than one product.

The actual data import sheet looks something like this:

OrderNumber ItemNumber Date Product PricePerUnit CostPerUnit

4967 1 10-May-10 widget 430.92 306.00

4967 2 10-May-10 doohickey 256.33 165.00

4968 1 11-May-10 doohickey 256.33 165.00

The actual data export sheet looks something like this:

Date Name Account Account Type MEMO Split Account Amount

10-May-10 vendor1 Bank Credit Card 4967 Cost of Goods Sold -306.00

10-May-10 vendor1 Bank Credit Card 4967 Cost of Goods Sold -165.00

11-May-10 vendor2 Bank Credit Card 4968 Cost of Goods Sold -165.00

My problem is that I cannot figure out how to return the total only, and not individual amounts, for orders

with more than 1 item. I.E., how can I tell excel to return each OrderNumber only once, along with

the total for each, like this:

Date Name Account Account Type MEMO Split Account Amount

10-May-10 vendor1 Bank Credit Card 4967 Cost of Goods Sold -471.00

11-May-10 vendor2 Bank Credit Card 4968 Cost of Goods Sold -165.00

There is a little more to this, as I am also trying to include shipping costs, but I Am still trying to

figure out how to return the data using msq, which I am finding a little quirky to use at all, but I have

managed to at least get it to work on a basic level.

Anyhow, is there anyone there who can help? I would be very grateful and much obliged.

Thanks in advance.

AJ

**Answer**:

To calculate the total amount for each order number you can create a pivot table. I will also provide a solution using array formula.

### Calculate the total for orders with more than one item with pivot table (Excel 2007)

**Create Pivot table**

- Select range A1:H4
- Click "
**Insert**" tab on the ribbon - Click "Pivot table" button
- Choose where you want the pivot table to be placed
- Click OK

**Setup Pivot table**

- Drag field
**Date**down to "**Report Filter**" area - Drag
**MEMO**to "**Row Labels**" area - Drag field
**Amount**to "**Values"**area

**Total amount for each unique order number**

### Calculate the total for orders with more than one item with excel formula

**Array formula in A11:**

Copy cell A11 and paste it to the right as far as needed. Then copy the row and paste it down as far as needed.

**Formula in H11:**

Copy cell H11 and paste it down as far as needed.

**Download excel sample file for this tutorial. **

pivot table.xlsx

(Excel 2007 Workbook *.xlsx)

**Functions in this article:**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

**SMALL(**array,k**)** Returns the k-th smallest row number in this data set.

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**COLUMN(**reference**)
**returns the column number of a reference

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Follow hyperlinks in a pivot table

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

How to create a dynamic pivot table and refresh automatically

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]

Change pivot table data source using a drop down list

In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]

Excel 2007 pivot table: Count unique distinct records (rows)

Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The […]

This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to […]

Google spreadsheets: Pivot tables

This week Google Docs introduced Pivot Tables in Google Spreadsheets. I am not that familiar to google spreadsheets but I had to […]

Sum security holdings monthly and yearly in a pivot table

Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form