Below is my Workbook Sheet1

Am expecting sheet2 like below, Total Item column (Using countifs I can get but Sub_Item1,2 and 3 How do I use Match Index in Excel)

CodePudding user response：

Use countifs to count total_item =COUNTIFS($A$3:$A$12,D3). For sub items use below formula. Then drag down and right as needed. If need to handle errors then use IFERROR() function.

=INDEX($B$3:$B$12,AGGREGATE(15,6,(ROW($A$3:$A$12)-ROW($A$2))/($A$3:$A$12=$D3),COLUMN(A$1)))