Distinct Count using pivot table

[debbiepy]

MS Excel 2007

I want to do a distinct count using a pivot table:

Date Deal#
2/5/06 2 Result
2/5/06 2
2/1/06 3 Feb 2
3/1/06 4 March 2
3/1/06 4 total deals 4
3/5/06 5

I want to group by month and only count unique deals per month. How exactly can I do this using pivot tables.

 

[GlennUK]

Why must this be done in a pivottable? Why not by using formulae?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Not doable in pivottable without an extra column in the data set

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[debbiepy]

How can I do this using a formula? I am not familiar with this

 

[xlbo]

Best bet is to get rid of dupes before doing the pivottable

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[debbiepy]

they are not dups. It is deals with multiple products.

For example Deal 1 bought three products. I get the deal number three times but I still need the detail per product line in addition to the distinct count of deals.

 

[xlbo]

2/5/06 2 <<< DUPE
2/5/06 2 <<< DUPE
2/1/06 3

If you can delete dupes then a simple count of deals should do the trick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

one way is sorting on deal number and using the following formula in another column (starting at row 2) (assuming Deal# in B):

=--(B1=B2)

You can then use a sum of that column in your pivottable.

Cheers,

Roel