Countif with Merged Cells in excel-CodePudding

I have the below table. From this, I would like to get the count of the subject each class has as shown in Columns E and F using formulas.

I used the countif formula, but it didn't work because of the merged cells.

Is it possible to get the count of the subjects keeping the merged cells?

CodePudding user response：

One way of doing this could be through REDUCE():

Formula in D1:

=LET(a,SCAN(A2,A2:A21,LAMBDA(a,b,IF(b="",a,b))),REDUCE(A1:B1,UNIQUE(a),LAMBDA(b,c,VSTACK(b,HSTACK(c,SUM(--(a=c)))))))

Note that SCAN() acts like a filldown function. Also note that merged cells are evil!

CodePudding user response：

=LET(a,A2:A21, 
     c,DROP(REDUCE(0,a,LAMBDA(x,y,VSTACK(x,IF(y="",TAKE(x,-1),y)))),1),
     u,UNIQUE(c),
     m,MMULT(--(TRANSPOSE(c)=u),SEQUENCE(COUNTA(c),,1,0)),
HSTACK(u,m))

Where c creates the unmerged equivalent of column A replacing blanks with the value above and m creates the count for each value that equals the unique values u in the unmerged version of column A.

Edit: as Mayukh commented we could replace MMULT by MAP/SUM:

=LET(a,A2:A21,
     c,DROP(REDUCE(0,a,LAMBDA(x,y,VSTACK(x,IF(y="",TAKE(x,-1),y)))),1),
     u,UNIQUE(c),
HSTACK(u,MAP(u,LAMBDA(x,SUM(--(c=x))))))

CodePudding user response：

Here is another way using MAP()

• Formula used in cell D2

=LET(a,LOOKUP(ROW(A2:A21),ROW(A2:A21)/(A2:A21<>""),A2:A21),
b,UNIQUE(a),
c,MAP(b,LAMBDA(x,SUM(--(x=a)))),
VSTACK(A1:B1,HSTACK(b,c)))

We can use SCAN() as well, since

• Using SCAN()

=SCAN(A2:A21,A2:A21,LAMBDA(x,y,IF(y="",x,y)))

• Using LOOKUP()

=LOOKUP(ROW(A2:A21),ROW(A2:A21)/(A2:A21<>""),A2:A21)

Few friendly suggestions:

Merged cells may help you arrange values in a meaningful way, but they come with problems;

You can't sort columns when using merged cells;

You can't use the Filter option, even if you could some bigger problems waiting for you to make that work out, try that out;

Instead use Center Across Selection which is a much better alternative to merging.

Last but not least, I have seen most experts suggests and recommends in avoiding merged cells. So please avoid merged cells.