How to count similar groups of items in a list using Excel, filtered by multiple conditions?

Village.Idyot 0
Viewed 22

I frequently work with Excel sumifs/maxifs/minifs/countifs but I am stuck. I am trying to count the number of grouped elements in a list, filtered by multiple conditions, using a concise formula in a single column (manually copied down the range) using one of my favorites mentioned above and without using Excel's array function where you press Ctrl/Shift/Enter, and without using VBA.

In the illustration below, in the yellow cells I count the number of "blocks" where Element and Group are the same. The "blocks" of same Element/Group are circled with borders in columns B and C. This column F formula works fine based on the conditions that Element and Group are the same.

Now I'm trying to expand the formula whereby any Group = 0 is excluded from a "block" (is ignored), as shown in the blue cells. I tried adding to the filter() starting in cell F3 the condition C$3:C3 <> 0 (and copied down), and it didn't work. Any suggestions for adding another condition to the filter, that Group <> 0?

enter image description here

unique grouping countif filter excel
1 Answers

You'd have to add a condition for C3 not being zero, e.g.

=SUMPRODUCT(ISNUMBER(FIND(B3,UNIQUE(FILTER(B$3:B3&C$3:C3,(B$3:B3<>"")*(C$3:C3<>0)))))*(C3<>0))

So the filtering part is right because it doesn't add groups where C3=0, but the existing formula would just give you one less than the number of groups so it works for the first one, but not later ones.

Tom Sharpe0
Related