Excel 2010: How do I formulaically count the total number of cells within a range that containts merged cells?

Question

I've been using =ROWS(my_range)*COLUMNS(my_range) to count the total number of cells within a single column.

I'm now trying to count the total number of cells across a range that contains (unavoidably) merged cells and I'm getting a #REF error using the above formula.

I've also tried: =COUNTA(my_range) & "/" & COUNTA(my_range) + COUNTBLANK(my_range) which is giving me a #VALUE! error.

In my last attempted I hoped that =ROWS(my_range) might work as I've only got merged columns, not merged rows. However this is giving me a #REF error. `

All I need is the total number of cells that exists within my_range

Thank you

That gives me a `DIV/0!` error which I've literally never seen before. I didn't know an error could split like that. — , Oct 12 '18 at 14:50
`=COUNTA(my_range) /(COUNTA(my_range) + COUNTBLANK(my_range))` — Scott Craner, Oct 12 '18 at 14:53
Stepping through the evaluation and I'm getting `0/(0+(#VALUE!))` with that which is progress! — , Oct 12 '18 at 14:57
Can't reproduce the error - what is the area that `my_range` refers to? — BigBen, Oct 12 '18 at 15:24
A few thousand cells, across columns B & C on a single worksheet, some merged some not and not consistent. It was a nightmare getting them all into `my_range` in the first place. Range definitely works as I use it for bunch of other stuff. — , Oct 12 '18 at 15:45
@L23P What's the actual address? `COUNTBLANK` will return a `#VALUE!` error if `my_range` has more than one area. — BigBen, Oct 12 '18 at 15:57
`= COUNTA(range)` will work to count total cells (in a range with merged cells) ***if*** all cells in `range` are not blank. I'm not sure if this is possible to count the number of cells in a range that contain merged cells if some of the cells are blank, because there is no way (that I know of) to distinguish between a merged cell and a blank cell. — ImaginaryHuman072889, Oct 12 '18 at 18:35
@ImaginaryHuman072889 that's why OP is trying to use both `COUNTA` and `COUNTBLANK`. I don't think the point is to distinguish between merged cells and blank cells. — BigBen, Oct 12 '18 at 18:41
@Pierre44 if the solution could run on launch certainty! In an annoying workout I've manually entered a 1 into a helper column and used the count function, this isn't ideal but serves. — , Oct 16 '18 at 16:10

Pierre44 · Answer 1 · 2018-10-17T10:09:19.017

So using merged cells make it really annoying to work with a lot of formulas, so I wrote a VBA solution using Arrays:

First this function will go through the range and each time it recognizes a merged cell, the code will add the cells to an Array.

Later when the loop comes to a cell marked as "merged" (= is in the array), the count will skip it (thanks to this topic: Check if a value is in an array or not with Excel VBA).

Option Explicit

Function CountCells(RA As Range) As Long

Application.Volatile

    Dim i As Long
    Dim a As Long
    Dim i2 As Long
    Dim a2 As Long
    Dim RowCount As Long
    Dim ColCount As Long
    Dim k As Long
    Dim R1 As Long
    Dim R2 As Long
    Dim C1 As Long
    Dim C2 As Long
    ReDim iArray(1 To 1) As Variant

R1 = RA.Row
R2 = R1 + RA.Rows.Count - 1
C1 = RA.Column
C2 = C1 + RA.Columns.Count - 1

k = 0

    For i = R1 To R2
        For a = C1 To C2

                If IsInArray(Cells(i, a).Address, iArray) Then
                    GoTo next_a
                End If

            ColCount = Cells(i, a).MergeArea.Columns.Count
            RowCount = Cells(i, a).MergeArea.Rows.Count

                If RowCount > 1 Or ColCount > 1 Then
                    k = k + RowCount * ColCount - 1

                    For i2 = i To i + RowCount - 1
                        For a2 = a To a + ColCount - 1
                            iArray(UBound(iArray)) = Cells(i2, a2).Address
                            ReDim Preserve iArray(1 To UBound(iArray) + 1) As Variant
                       Next a2
                    Next i2

                End If
next_a:
        Next a
    Next i

    CountCells = (R2 + 1 - R1) * (C2 + 1 - C1) - k

End Function


Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
Application.Volatile
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

Then you just need to use this function like this in your sheet:

=countcells(my_range)

or any other range instead of your range named my_range

Note: With Application.Volatile the function updates automatically, but only when you update the sheet with numbers but just not directly when you merge or unmerge cells.

Thanks @Pierre44 I'll try this now, call me stupid but do I use this in a normal module or does it need to go somewhere else? — , Oct 17 '18 at 12:21
In a normal module from the concerned workbook it is fine :) — Pierre44, Oct 17 '18 at 12:33

Excel 2010: How do I formulaically count the total number of cells within a range that containts merged cells?

1 Answers1