Formula to count only one duplicate value

[ericedholm]

Hi all,
I have found various solutions to this problem (such as removing duplicate values, but this is not quite what I want).

I have a list of invoice numbers in left column, and in the right column I only want to count the value once.

Like this:

Invoice ID Unique orders
Invoice 1 0
Invoice 1 0
Invoice 1 1
Invoice 2 1
Invoice 3 0
Invoice 3 1
Total 3


I had a formula that looked something like this before

=SUM(IF(FREQUENCY([@[Invoice real]],[@[Invoice real]])>0,1))
=IF(COUNTIF(K65:$K$9028,K65)=1,1,0)


However since I am using a table with dynamic range this formula seems to break every time I sort in a different way.

I am a bit stuck here, so any help would be much appreciated.

Thanks

[ericedholm]

To clarify,
I need something like this:

order example.jpg

[dhn46]

If your data set A1:B9 you can use formula:
Unique Invoice

Please Login or Register  to view this content.

Sum

Please Login or Register  to view this content.

[ericedholm]

Thanks for quick reply!
So my data is in a table, and I link it to a pivot table, so I use "structured references".
So the formula would look something like this (if Transaction number = unique invoice), however this is not working, I am not sure if it is possible to do what I am asking for with structured references?


=IF(ROW([@[Transaction number]])=LOOKUP(2,1/([@[Transaction number]]=[@[Transaction number]]),ROW([@[Transaction number]])),1,0)

Any ideas?

[AZ-XL]

another alternative solution
b2 cell =IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),1,0) then drag down