# 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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### acw

##### MrExcel MVP
Hi

Try the countif formula in the form

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

Adjust the range to suit.

Tony

#### martinsmonkey

##### New Member
Hi

Try the countif formula in the form

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

Adjust the range to suit.

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
0
Views
39
Replies
7
Views
148
Replies
1
Views
108
Replies
2
Views
75
Replies
9
Views
78