+ Reply to Thread
Results 1 to 13 of 13

Counting merged cells *Please Help!*

  1. #1
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Counting merged cells *Please Help!*

    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

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Counting merged cells *Please Help!*

    Welcome to the board, lindaung.

    The universal solution to every problem associated with merged cells is to unmerge them.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting merged cells *Please Help!*

    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).

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Counting merged cells *Please Help!*

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

  5. #5
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting merged cells *Please Help!*

    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"?

  6. #6
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Counting merged cells *Please Help!*

    Why just C24-C27 ? what the logic?

  7. #7
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting merged cells *Please Help!*

    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.

  8. #8
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting merged cells *Please Help!*

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

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting merged cells *Please Help!*

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

    while

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    = 6
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting merged cells *Please Help!*

    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

  11. #11
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting merged cells *Please Help!*

    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!

  12. #12
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Counting merged cells *Please Help!*

    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.....

  13. #13
    Registered User
    Join Date
    05-26-2015
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting merged cells *Please Help!*

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting Merged Cells as multiple values
    By jack.stapleton in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-15-2014, 03:49 AM
  2. Counting merged cells with line breaks
    By Cougar548 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2013, 09:22 AM
  3. Replies: 2
    Last Post: 11-12-2010, 02:04 PM
  4. Counting individual coloured cells that are merged.
    By JohnSan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2008, 01:11 PM
  5. counting colored cells when merged
    By DrKilbert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2005, 03:06 PM

Bookmarks

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