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.
 

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top