Counting merged Cells based on criteria

[leeksleeks]

Hi,

I have read a lot of forums discussing merged cells and how to calculate the number of merged cells based on the criteria given, however the formula below only returns the number of merged cells for the first set of merged cells and not for the entire column. If I wanted to count the number of merged cells in column D that had the word "Apple" in them and this was present in Cells D4:D10 and then D16:24 and finally in D45:D49 it would only return the count from the first instance so in this case it would return 7.

Here is the code i am using which is fairly generic on the forums:

Function MergedCellsCount(rRange As Range) As Integer    Application.Volatile
    MergedCellsCount = rRange.MergeArea.Cells.Count
End Function

and then the code I have in the worksheet which has the word Apple in cell A70 is:

=IFERROR(MergedCellsCount(INDEX(D4:D51,MATCH("*"&$A$70&"*",D4:D51,0))),"")

Anyone got any ideas how to return a value which counts every instance in a column with merged cells based on a keyword like Apple?

Cheers,

Alex

 

[Leith Ross]

Hello leeksleeks,

Try this UDF and let me know it needs adjusting.

Function MergedCellCount(ByRef Rng As Range, ByVal Criteria As Variant)


    Dim c       As Long
    Dim Cell    As Range
    Dim n       As Long
    Dim r       As Long
    
        Application.Volatile
        
        For c = 1 To Rng.Columns.Count
            For r = 1 To Rng.Rows.Count
                Set Cell = Rng.Cells(r, c)
                If Cell.MergeCells = True And (Rng.Columns(c).Column = Cell.MergeArea.Column) Then
                    If Cell = Criteria Then
                        n = n + Cell.MergeArea.Count
                        r = r + (Cell.MergeArea.Rows.Count - 1)
                    End If
                End If
            Next r
        Next c
        
        MergedCellCount = n
        
End Function

 

[leeksleeks]

Hi Leith.

I couldn't get this to work unfortunately as all the calculations went blank. If in excel you merge C5:C9 and enter "Shift 1" in there and then in C:10 write "Break" and then merge cells C:11:C15 and again write "Shift 1" the calculation for Shift 1 will return 5 instead of 10. Can anyone get that to return 10?