There are times where you may wish to report sub groups within departments. It can be difficult when the sub groups are varying lengths. Here is a solution to that problem. The formula will test for the first item in the sub group and return a result, then test for a second sub group and if found return a result.
The formula is as follows.
=IFERROR((OFFSET($B$7,MATCH($A$32,$A$8:$A$18,0)+
IF(COUNTIF($A$8:$A$18,$A$32)>(ROW(B32)-ROW(B$32)),(ROW(B32)-ROW(B$32)),NA()),0)),"")
Where A32 contains the Group name and column A contains the groups. Column B is a listing of the sub groups and B7 is the cell we will offset by.
I have put a total group in the chart to add the labels. This appears atthe bottom of the table. This grouping is to add a total to the chart.
To see how the Excel file works, ope the attached.
