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.
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.