I thought I was getting pretty good with Excel sumifs/maxifs/minifs/countifs but I am stuck on this one. I am trying to count the number of grouped elements in a list, using a concise formula in a single column (manually copied down the array range) preferably using one of my favorites mentioned above and without using Excel's annoying array function where you have to press Ctrl/Shift/Enter, and without using VBA. I illustrate and explain the desired outputs of block group counts below in yellow. Any ideas for a formula that does this?
The << Formula columns are only illustrating countifs() using a single condition and multiple conditions, respectively.
CodePudding user response：
Try below formula-
=BYROW(B3:B17,LAMBDA(x,SUM(--((UNIQUE(FILTER(C3:C17,(B3:B17=x)*(ROW(C3:C17)<=ROW(x)))))<>""))))
CodePudding user response：
This uses a spill range array (same as ctl shift enter) without pressing it. Hopefully you have that functionality.
=SUMPRODUCT(--ISNUMBER(FIND(A2,UNIQUE(FILTER(A$2:A2&B$2:B2,A$2:A2<>"")))))