# Formula not pivot

#### martinsmonkey

##### New Member
I have a worksheet with no column headers, I need to tell how many times a part number appears in the sheet and get a total for each part that is required. The part numbers have the format D?????A or D?????B or D?????C and can be anywhere on the worksheet.

I can insert a header row and do via a pivot table but would like a formula if possible.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### acw

##### MrExcel MVP
Hi

Try the countif formula in the form

=countif(a1:b50,"D*A")

Tony

#### martinsmonkey

##### New Member
Hi

Try the countif formula in the form

=countif(a1:b50,"D*A")

Tony

Thanks Tony
have used countif only problem is I need to know how many of each part starting with D are needed. e.g. D12345A D12345B D12345A D12345C D12345D D12345F will tell me I need 2 D12345A and 1 each of the other parts.
The parts always have 6 or 7 chars starting with D and (not always) ending in a letter and can appear anywhere in the worksheet

#### acw

##### MrExcel MVP
Hi

IF you want the count for all parts that start with D, then

=countif(a1:b50,"D*"). If you want the individual component counts, then you can modify to incude the suffix and get a count of each matching part.

Tony

Replies
1
Views
144
Replies
4
Views
513
Replies
2
Views
256
Replies
1
Views
130
Replies
3
Views
188

1,141,915
Messages
5,709,320
Members
421,628
Latest member
Big Boss

### 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.

### Which adblocker are you using?

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

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