My first [Q] Count element & sub-elements

[m.hamadeh]

Hello all, I’m happy to join this forum and i hope we all have a lot of benefits here.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have a record with hundred of master elements like “60OC-000####” and each element has some sub-elements like “60OC-000####.##” as example see the table below.<o></o>
<o> </o>
I can count the total number of sub-element. However I’m facing difficulties to count how many master elements is there. I will be happy to have some help.<o></o>
<o> </o>
60OC-0002744.01<o></o>
60OC-0002801.01<o></o>
60OC-0002801.02<o></o>
60OC-0002973.01<o></o>
60OC-0003041.01<o></o>
60OC-0003041.02<o></o>
60OC-0003041.05<o></o>
60OC-0003041.06<o></o>
60OC-0003041.21<o></o>
60OC-0003041.22<o></o>
60OC-0003041.24<o></o>
60OC-0003041.25<o></o>
60OC-0003053.01<o></o>
60OC-0003053.02<o></o>
60OC-0003053.03 <o></o>

 

[JackDanIce]

Welcome to the board

In a blank column, enter this formula:

=LEFT(A1,LEN(A1)-3)

And drag it down to the last row you have data in.
In the column next to this, enter the formula:

=COUNTIF($A$1:$A$10,J1)

Change J to whatever column the first formula I suggested is in

You could use Advanced Filter after entering the first formula I provided to get a unique list of master values before applying the COUNTIF formula

 

[m.hamadeh]

The second formula worked and I know now how many sub element for each master, but I still don’t know how many master element in total I have.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Example: in this list above the total is 15 Sub-elements coming from 5 master elements. I need this information to come automatically.<o></o>

 

[JackDanIce]

My mistake, that second formula should be:

=COUNTIF($J$1:$J$10,J1)

Where J is whatever column you put the first formula into and change 10 to whatever the last row of data is

 

[m.hamadeh]

Thanks it worked now. Further more I’m able to count the master element using the following formula.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=SUMPRODUCT((B12:B28<>"")/COUNTIF(B12:B28,B12:B28&""))