# Counting cells that have unique text and numeric values

#### Outlaw134

##### New Member
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?

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
E2: 1234

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=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))
``````

Replies
4
Views
183
Replies
0
Views
720
Replies
3
Views
656
Replies
3
Views
230
Replies
4
Views
663

1,196,134
Messages
6,013,642
Members
441,777
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back