Count Cells in a merged Cell

[23-zeros]

hello people, I'd like to count Cells that have values in a merged format, is this even possible? tnx indeed.

Book1.xlsx

[mangesh.mehendale]

Welcome to forum... please explain how these are 19 and 14

[23-zeros]

@mangesh.mehendale Thank you so much, sorry to post this thread before I introduced myself, will do asap.
imagine there were no merged cells in this sheet, and we wanted to count the duplicated values and also non duplicated in merged cells, that's what I'm after.

[cyiangou]

Vba Function:

Public Function MergeCellCount(Rng As Excel.Range) As Long
    Dim Cell As Excel.Range
    Dim Count As Long
    Count = 0
    For Each Cell In Rng
        If Not IsEmpty(Cell.MergeArea.Cells(1, 1).Value) Then
            Count = Count + 1
        End If
    Next
    MergeCellCount = Count
End Function

merge_cell_count_cy.xlsm

[23-zeros]

Vba Function:

Public Function MergeCellCount(Rng As Excel.Range) As Long
    Dim Cell As Excel.Range
    Dim Count As Long
    Count = 0
    For Each Cell In Rng
        If Not IsEmpty(Cell.MergeArea.Cells(1, 1).Value) Then
            Count = Count + 1
        End If
    Next
    MergeCellCount = Count
End Function

Attachment 411577

Wow, thank you man, That's exactly What I meant, how should I return the favor?

[protonLeah]

When cells are merged, only the upper left cell holds the value. COUNT is not counting cells, it is counting the number of cells with numbers; therefore, 10 not 19. You merged A5:A7 and entered 78 in the merged area, but only cell A5 is holding the 78 so count is one not three.

[23-zeros]

When cells are merged, only the upper left cell holds the value. COUNT is not counting cells, it is counting the number of cells with numbers; therefore, 10 not 19. You merged A5:A7 and entered 78 in the merged area, but only cell A5 is holding the 78 so count is one not three.

I know merging is not recommended at all, but I needed this for a lot of sheets that the end user works with and knows nothing about excel at all, I tried to unmerge the cells and entered the same value in them, but it's hard to work due to the users eyes, but I think I could use the cyiangou's VBA function to solve this. tnx anyways

[cyiangou]

This also had me arguing for awhile, but 23-zeroes spec is exactly consistent with his expected result.

count Cells that have values in a merged format

or 'count cells whose containing mergeareas have values entered'

[FDibbins]

Based on the sample answers (19 and 14), this cannot be done with regular formulas. As Ben said a merged cell (of however many cells) becomes 1, so excel will count that sample as 10 and 5

[cyiangou]

A pleasure. An 'Add Reputation' to the left of my post would be good. Thanks!

(also mark as SOLVED in the thread tools at the top of the page)