Sum Order and Vendor Orders from Pivot Table for Given Month

[Guest]

I could use some assistance with counting the number of orders and vendor
orders for a given month. I have Date Order Accepted in one column, Delivery
Order in another, and Vendor Order in another. Because multiple orders (both
DO and VO) can be issued on the same day, the Date Order Accepted is listed
once for a given date, and the two orders associatd with it are obviously
grouped together, but with DO having one order and VO having the other. How
do a count the number of orders issued in a given month? I believe if I
could figure this out, I could expand my summary worksheet to capture the
total value of the orders reported for a given month. Of course, if someone
could help with that, it would be appreciated as well.

 

[Debra Dalgleish]

Could you type a few rows of sample data into a message, and explain the
result that you'd like to see in your pivot table, from that sample data?

 

[Guest]

DO# VO# RFX# Date Order Accepted Total Amount
NETCENTS 10091 RFQ10091 12/6/2006 24072.02
0006UH01 RFQ9470 12/6/2006 6994940.12
5T03 NETCENTS 7076 RFQ7076 12/6/2006 1512.82
RS24 RFQ10159 12/22/2006 4384730.91
RS25 RFQ10160 12/22/2006 488992.8

 

[Guest]

I cut and pasted this from the pivot table. As you can see there are a few
orders having the same Order Accepted date, but different order numbers. I
want to count the number of orders accepted during a given month. I have a
separate worksheet that summarizes the information by month/year, and was
trying to create the formula to capture the number of orders accepted for
each month/year. The other thing is that I want to sum the total amount for
the given month/year as well.

DO# VO# RFX# Order Accepted Total Amount
10091 RFQ10091 12/6/2006 24072.02
0006UH01 RFQ9470 12/6/2006 6994940.12
5T03 7076 RFQ7076 12/6/2006 1512.82
RS24 RFQ10159 12/22/2006 4384730.91
RS25 RFQ10160 12/22/2006 488992.80

 

[Debra Dalgleish]

You could add a column to the source data, and count the orders in each
row. Then add that new field to the pivot table, to get your totals.

For example, if the DO# and VO# are in columns A and B, use this formula
in the new column: =COUNTA(A2:B2)
and copy it down to the last row of data.

In the pivot table, add the new field to the data area, using the sum
function.
You can group the date field by year and month, to get the counts for
each period.

 

[Guest]

The source data comes from data reported from XML, which is in turn provided
to me in the form of Excel as a result of exporting it via SQL. When an
order has multiple line items (products if you will), the Excel data passed
to me has the order number several times based on the number of different
items ordered. For instance, if Order number RS03 has 3 items associated to
it, then the Excel data sent to me has 3 rows, with the order number showing
up in each row, but within the same column.