Pivot Table: Count total instances across columns

[serenepdx]

Hi,

I'm trying to do the equivalent of a count() function across the columns of a pivot table, but I can't seem to come up with the right formulation.

For example, I have a list with salesperson, product, sale price, and date. I want to generate a pivot table that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Printer[/TD]
[TD]#[/TD]
[TD]Phone[/TD]
[TD]#[/TD]
[TD]Copier[/TD]
[TD]#[/TD]
[TD]Product Lines Sold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]$300[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$5,000[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]$100[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD]1[/TD]
[TD]$1,000[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

It will total and count the sales for each sales person and product. It will also count how many different types of products each sales person sold. This example assumes that each printer, phone or copier sells at the same price, but they will sell at different prices. I am using Excel 2003. I have found that putting a count() function off to the right of the table does not work.

I learned about this forum by finding and implementing
http://www.mrexcel.com/forum/excel-questions/518189-pivot-table-drill-down-new-window.html
and it worked great. Thanks!

 

[serenepdx]

The rows are sales persons. The columns are sum of sale price, and count of sale price. The rows are the products. What I would envision is adding another data item that returns 1 if there was one or more sale for the product and 0 otherwise, but maybe there's another solution?

 

[serenepdx]

I could never get the grand total to add up correctly, and that appears to be a known problem, so I went back to add a calculate field to my original sheet that gives a 1 for each unique occurrence of product and sales person, but if there's a way to make it work purely in the pivot table, I'd love to learn!