Count function in merged cells

[Siddharth Ananthula]

Hello Chandoo.

I am trying to apply the count if function on a column that has merged cells. I do not want to unmerge my cells as I am afraid that in the sheet that I am using, it will lead me to supply the wrong statistics.

I have attached a sample sheet. Can you please help me. I am preferring not to use a VBA code but in case we need to, can you please attach the code in the sheet provided.

Thank you in advance.

Sid

 

[Somendra Misra]

Hi Sid,

First, a general advise, avoid using merged cells. It create a lot of problem when you are analyzing data through formulas or through Data analysis features available in Excel.

Second, try below formula in G1 and copy down:

=SUMPRODUCT(--(LOOKUP(ROW($A$1:$A$22),ROW($A$1:$A$22)/($A$1:$A$22<>""),$A$1:$A$22)=F1))

Third, your Gavaskar in F3 has an extra space in the last.

Fourth, In G12, you have not put any result, but there should be 1.

Regards,

 

[bobhc]

Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you merge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.

.

 

[Ramesh Deo]

Hi Sid,

First, a general advise, avoid using merged cells. It create a lot of problem when you are analyzing data through formulas or through Data analysis features available in Excel.

Second, try below formula in G1 and copy down:

=SUMPRODUCT(--(LOOKUP(ROW($A$1:$A$22),ROW($A$1:$A$22)/($A$1:$A$22<>""),$A$1:$A$22)=F1))

Third, your Gavaskar in F3 has an extra space in the last.

Fourth, In G12, you have not put any result, but there should be 1.

Regards,

Hello SM Sir,

Have a very good morning!!!!

Actually I was saying, in this you solution can we use result vector $B$1:$B$22=F1 instead of $A$1:$A$22=F1...

 

[Siddharth Ananthula]

Gentlemen - thank you so much for the reply.

My actual conundrum is the following:

In the attached sheet, please find the columns A, B and C, they form the pattern in which my data is arranged currently. Column E represents the information by which I need to attach my data.

the desired result is entered manually in the table in yellow.

how I automate this process? Please ignore information in Column H, it is only for explicit description.

Thanks again.

Sid

 

[NARAYANK991]

Hi Siddharth ,

In G20 , enter the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(IF($E$2:$E$16=F20,1/COUNTIFS($E$2:$E$16,F20,$C$2:$C$16,$C$2:$C$16)))

Copy down.

The text Basheer Bhag in cell F23 has an extra trailing space.

Narayan

 

[Somendra Misra]

Hi Sid,

Well you requirement went on a change from you original question.

In this case apart from Narayan Sir suggestion, here is one more option, Array formula, so must be entered through Ctrl+Shift+Enter.

=SUMPRODUCT(--(FREQUENCY(IFERROR(MATCH(IF(F20=$E$2:$E$16,$C$2:$C$16),$C$2:$C$16,0),"e"),IFERROR(MATCH(IF(F20=$E$2:$E$16,$C$2:$C$16),$C$2:$C$16,0),"e"))>0))

And as Narayan Sir pointed out there is extra trailing space in the text on cell F23, remove it and than test the formula.

Regards,