Counting references in a pivot table once

[kosciosco]

Hello,

I have a table which shows a list of invoices and the amounts of each invoice along with the job reference that each invoice relates to. In any given month there can be multiple invoices which relate to the same job reference.

I want to create two pivot tables. The first shows the amount invoiced in each month related to the job reference - easy

The second I want to show the number of job references in each month, however I can't use count because if the job reference appears multiple times, it will be counted multiple times. I only want to count each reference once when it appears in the month.

Any ideas how to do that? I've tried all the value field settings and none of them do what I need

Thanks

[shj92922]

Can you upload sample of your sheet

[JeteMc]

My thought is to add a column to the source data.
Let's say that the job references are in column C, the formula that populates the added column might read: =IF(COUNTIF(C$2:C2,C2)=1,1,0)
When you set up the second pivot table you would use the job references as the row labels and drag the heading for the added column into the values area.
If this isn't helpful then, as shj2922 suggested, please upload a sample.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.