How to count number of sub groups under each main group

**Mohamed Zayed** · 05-08-2024, 03:52 AM

Capture.JPG

I have excel sheet with 3 columns Groups, Sub groups, Sub Sub groups

then, I have another table with list of main groups, where for each main group I want to count how many sub groups / sub sub groups are under.

Main Group Sub Groups Main Group Count of sub groups
A M1 A 2
A M1 B 1
A M2 C 3
B U1 D 1
B U1
B U1
C L3
C L4
C L5
D L6

**AliGW** · 05-08-2024, 03:59 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**TMS** · 05-08-2024, 04:33 AM

Try, in E2:

Formula:

Please Login or Register  to view this content.

If that is a Table, it should AutoFill. If not, just copy down.

**Glenn Kennedy** · 05-08-2024, 05:01 AM

Try this... which counts UNIQUE values, not ALL values:

=SUM(--(FREQUENCY(IF(($A$2:$A$11=E2)*($B$2:$B$11<>""),MATCH($B$2:$B$11,$B$2:$B$11,0)),ROW($A$2:$A$11)-ROW(A2)+1)>0))

see file.