Excel Count Macro

[pbreezy]

Hi,

I have an excel 2016 spreadsheet with about 1000 rows of data. Each row has a name like ***_type1, or ***_type2, or ***_type3, etc. They also have further subdivisions, another column has a number (1,2,3,4, etc.) I would like to keep track of how many rows of each type the spreadsheet has and also how many are within each subdivision.

For example if the spreadsheet contains 100 rows of type1, I would like it to show on a separate sheet that I have 100 rows of type1 and 20 are in subdivision 1, 30 are in subdivision 2, 50 are in subdivision 3.

Does anybody have a macro that could do this or know a way I could make this happen?

Thank you!

[mdmackillop]

Add a column to list each type and then create a pivot table. Post a sample if you need further assistance.

[pbreezy]

Add a column to list each type and then create a pivot table. Post a sample if you need further assistance.

Thank you mdmackillop for the response I didn't think of that.

So I created a new column with a formula that would strip the beginning part of the name to use for the pivot table. Shown below.

=REPLACE(C2,1,FIND("_",C2),"")

C2 is where the name is held. The only problem I had here was that in some of the names there are multiple underscores so the formula would only remove the first instance. So something like ***_type12 would work fine but ***_***_type12 would not. Do you know a better way to do this?

[mdmackillop]

=SUBSTITUTE(RIGHT(C2,6),"_","")

[pbreezy]

Thanks this is exactly what I was looking for!

[Lina001]

I have been searched the way to solve this problem for a long time.