3

I have the below table. From this, I would like to get the count of the subject each class has as shown in Column F using a function. Like as how many subjects we have in Class A, how many subjects we have in Class B, and so on.

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

enter image description here

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

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Cath
  • 125
  • 10
  • Does this answer your question? [Excel COUNTIFS with merged cell](https://stackoverflow.com/questions/66406904/excel-countifs-with-merged-cell) – PeterT Dec 05 '22 at 17:58
  • I did see this post. In that case, the number of the cells merged is always same which is 6. But in my case, the number of cells merged is always not the same. It depends. Sometimes it will be 3, sometimes 4 or 10 or 20. – Cath Dec 06 '22 at 07:34

3 Answers3

2

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

enter image description here

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!

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    this is the shortest one because you build on the fly the header too, but I don't know how efficient is `REDUCE/VSTACK` for a large data set. I am just curious. For example, compare to this one: `=LET(gr, SCAN(A2, A2:A11, LAMBDA(ac,g, IF(g="", ac, g))),ux, UNIQUE(gr), m, MAP(ux, LAMBDA(g, SUM(gr=g))), VSTACK(A1:B1, HSTACK(ux, m)))` or instead of `SUM` using: `ROWS(FILTER(gr, gr=g)` – David Leal Dec 05 '22 at 22:22
  • 1
    Yes I have tried using the `FILTER()` it works fine, not tested with larger datasets, but the one with `REDUCE()` & `DROP()` works lightning fast without any flaws. I have commented on P.b. post, I have tested on 100K rows of data. [Tested_Query](https://stackoverflow.com/a/74173331/8162520) – Mayukh Bhattacharya Dec 05 '22 at 22:24
2
=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))))))
P.b
  • 8,293
  • 2
  • 10
  • 25
  • can we use `MAP()` instead of `MMULT()` like this way? `=LET(a,A2:A21,c,DROP(REDUCE(0,a,LAMBDA(x,y,VSTACK(x,IF(y="",TAKE(x,-1),y)))),1),u,UNIQUE(c),VSTACK(A1:B1,HSTACK(u,MAP(u,LAMBDA(x,SUM(--(c=x)))))))` I found its working what do you say – Mayukh Bhattacharya Dec 05 '22 at 21:54
  • 1
    Nice one. Have you tested both versions on larger sets? Does MAP allow bigger sets of data than MMULT? – P.b Dec 05 '22 at 22:13
  • Bigger sets mean how much you say, approx. For bigger sets I will never opt formulas as well, will go for vba or power query, but still i will test give me a rough idea. But I have credited @JvdV Sir earlier `DROP(REDUCE(0,a,LAMBDA(x,y,VSTACK(x,IF(y="",TAKE(x,-1),y)))),1)` this one has made a huge significance it works faster better and less flaws, I have tested this with 100K rows of data. works very fine. amazing `REDUCE()` function. Deadly combination of `DROP()` & `REDUCE()` and you have executed them very well on this one. – Mayukh Bhattacharya Dec 05 '22 at 22:22
2

Here is another way using MAP()

enter image description here

• 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 JvdV Sir has already shown that option, hence using the OLDSCHOOL Function LOOKUP(). Which simply does the same thing as shown using SCAN() refer the screenshot below,

enter image description here

• 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.
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • Hi Mayukh, I tried. Unfortunately, I got the #SPILL error with the above solution. My excel version is (Version 2210 Build 16.0.15726.20188) 32-bit. – Cath Dec 06 '22 at 11:07