Search code examples
excelgroupingcountifsumifs

How to count similar groups of elements in Excel using multiple criteria?

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.

enter image description here

Post possible solution:

Now showing a scenario where the solution doesn't hold up (holds up in all other 16 scenarios I ran but not this one ???), see the orange cells with strange output versus expected output in yellow:

enter image description here

Solution

  • OK well in the first iteration of your question what seemed like a long time ago we all assumed that you wanted to count the total number of unique groups you have got so far. Now it's looking a bit different - the example implies that if both the element and group are repeated later on you want to revert back to the count of that element and group only and not the total count. I think what you want is a match like this:

    =MATCH(A4&B4,SORT(UNIQUE(FILTER(A$4:A4&B$4:B4,A$4:A4<>""))),0)-
MATCH(A4&"*",SORT(UNIQUE(FILTER(A$4:A4&B$4:B4,A$4:A4<>""))),0)+1

    Could be simplified using Let but it's late here and it's tentative anyway.

    enter image description here

    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.

    EDIT

    Here is my revised formula - plz try it.

    =LET(range,A4:B$4,
unique,UNIQUE(range),
uniqueElement,INDEX(unique,0,1),
sortby,SORTBY(unique,uniqueElement,1),
sortElement,INDEX(sortby,0,1),
sortGroup,INDEX(sortby,0,2),
MATCH(1,(sortElement=A4)*(sortGroup=B4),0)
-MATCH(A4,sortElement,0)+1)

    You could also use Take instead of Index. I've removed the filter for clarity because there aren't any blanks in the test data.