I am trying to count groupings of elements in a list, in Excel, with groupings defined by 2 conditions: same Element and same Group code, as shown in the image below. The column C formula seems to throw errors as shown in orange highlighting in cells C9-C11. The formula for column C is displayed starting in column D. My expected grouping count is shown in column L with explanation starting in column M. Cells L9-L12 show what I expect the element count to be for Element X/Group 0.

Is there a correct formula for counting multiple-criteria groupings in Excel 365? I have tried various iterations of countifs() and sumproduct (See column D of the screencap) with no luck yet.

CodePudding user response：

OK well in the

However the sorting assumes that the group keeps on increasing within each element and that wasn't the case in the first iteration of your question. You could try Sortby just sorting on the Element but that will have to wait till tomorrow.