I am having issues with count/sum with my merged cells. Sometimes the merged cells are counting the contents as 1 and sometimes as multiple cells. I would like for excel to count the merged cells as 1. For example, I have cells c24-c25 merged into one cell with "1" in the cell. On the bottom right hand corner of the excel spreadsheet, it is stating that the selected cells has count:2 and sum: 2 but I would like for it sum as 1 and count as 1.

I also have cells c10-c13 merged with "1" in the cell. This time excel is reading it as count of 1 and sum of 1. Does anyone know why excel reads these 2 merged cells differently and how can I get it to read as one?

Merged Cell Example.xlsx

Any help is greatly appreciated!

Thanks,
Linda

Welcome to the board, lindaung.

The universal solution to every problem associated with merged cells is to unmerge them.

Thanks! But I can't unmerge these cells. This is a work template and I am not allowed to edit it. Is there a way for me to work around this as I want to count the merge cells as 1. Also, if I did unmerge the cells, it would count it as multiple cells (which I don't want to do).

I see the file but confused, what the criteria? and what the results?

Hi Azumi,

I'm trying to sum the contents of the cells in the file. However because some of the cells are merged, it seems like excel is double counting the value. For example, in cells C24-C27, the sum is showing as 4 instead of 2. I want it to total as 2. Does that make sense and how do I get it to sum "properly"?

Why just C24-C27 ? what the logic?

Hi Azumi,

I'm just using C24-C27 as an example of what excel is doing. It is counting it as 4 as opposed to 2.

What formula can I use to add up cells C24-C27 so that the sum equals 2 as opposed to 4?

Formula:
`Please Login or Register  to view this content.`
= 1

while

Formula:
`Please Login or Register  to view this content.`
= 6

For some reason whoever created your template didn't unilaterally do the merge cell the same way for every row

C25 and C27 still manage to hold data in them even though it is merged (and in turn hidden)
subtotal 109 = sum ignore hidden values

actually first time I've seen merged cells retain data like that i didn't even think it was possible...the template created probably made it via some macro to force it as normal data entry shouldn't normally allow it

Hi humdingaling- you're right...it does seem like something is hidden. Is there a quick way for me to find out which cells hidden? I have several hundred lines and would hate to go through each one.
Also, the subtotal 109 is not working for me. For the merged cells that are hidden, it's still double counting in the total. Thanks!

I see the problem, try to unmerged the merged cells, you'll see duplicates number in there, that why the results for C24:C27 is 4 not 2. With COUNTIF is possible on merged cells but with duplicates, no way.....

Hi Azumi,

Do you know how to easily identify the cells that have duplicate values? I have hundreds of merged cells in my file and can't easily tell which ones have hidden information and which ones don't.

Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1