Pivot tables and showing unique numbers

[james.cowan]

I am attempting to create a pivot table that shows a list of products (eg. mars, galaxy, kitkat) against the quantity (count) of a field containing invoice numbers, but the field contains several invoices that have the same number, so I would like the pivot table to show just unique numbers. (ie. I have 200 invoices but only 150 of them are unique numbers).

I know I can do this with advanced filtering or subtotals but ideally I would like to be able to do this in a pivot table. Does anyone know if this is possible? Thanks.

 

[AlohaJulio]

When you put the invoice field in the data section, double click on it, choose Count, then click options and choose index. That should give you the count of the unique invoices, not a count of all invoice lines

 

[james.cowan]

Unfortunately I'm struggling to get your solution to work...my pivot table just returns either '1' or a smaller number to several decimal places when I've checked there are more unique invoices relating to that product. Is there something obvious I am doing wrong here?

Thanks.