Counting cells that have unique text and numeric values

[Outlaw134]

I am working with Excel 2007 with Windows 7. I have a workbook that has a tab for maintenance data for 2014 and another for 2015. The data on these tabs is entered into a table that I do not want modified. I have a third tab that I put formulas in that will consolidate the information from the other two tabs. The information that I want specifically is the number of maintenance invoices per trailer and the sum of all of those invoices. I have tried to create a pivot table for this information, but have been unsuccessful. I believe this is due to the hyperlinks that I have in the original data tables. I have the sum of the invoices by vehicle number, but I need a formula that will count the number of invoices per trailer, but will only count unique values. Some of the invoice numbers are represented as text, since the invoice number starts with 0, so I will need a formula that will count text and numeric values. Below is an example of the data:

Trailer	Invoice
1234	'001050017
1234	10135
1234	10136
1235	'005070012
1235	10134
1235	10137
1235	10137
1236	10138
1236	10138
1236	10138

<tbody>
</tbody>

The information that I need returned is trailer 1234 has 3 invoices, trailer 1235 has 3 invoices, and trailer 1236 has 1 invoice. What is the best way to get this done?

 

[Aladin Akyurek]

E2: 1234

F2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=$E2,IF($B$2:$B$12<>"",
   MATCH("~"&$B$2:$B$12,$B$2:$B$12&"",0))),ROW($B$2:$B$12)-ROW($B$2)+1),1))