Formula not pivot

martinsmonkey

New Member
Joined
Oct 5, 2006
Messages
2
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.
 

Some videos you may like

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
Joined
Feb 13, 2004
Messages
4,814
Hi

Try the countif formula in the form

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

Adjust the range to suit.


Tony
 

martinsmonkey

New Member
Joined
Oct 5, 2006
Messages
2
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
Joined
Feb 13, 2004
Messages
4,814
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,727
Members
410,702
Latest member
clizama18
Top