Counting Number of Cells Including Merged Cells

[Daniel D.]

I am using Micosoft Excel 2000.
My worksheet has contiguous cells that represent a "trade
show booth." Most clients take only 1 booth but some
will take 2, 3, or 4 booths together (adjacent to one
another). With these clients I merge the cells together
and enter the client's name. In order for me to
calculate the percentage of occupied booths, I need to
know how many booths there are in total. For example:

20 contiguous cells - all 1 cell wide (whether occupied
or not gives me a TOTAL of 20 booths.

The same 20 contiguous cells - (12) 1 cell booths, (1) 2
cell(merged) booths, and (2) 3 cell (merged) booths gives
me a TOTAL of 14.

Does anyone know how I can create this TOTAL count?

Thanks in advance for your help.

Dan

 

[Tom Ogilvy]

The easiest would be to use an adjacent column (or row - same orientation as
the booths) and put in a 1 or leave blank. Then do your percentage on these
cells.

 

[Guest]

Thanks for your time.
This worksheet is for an end-user who is not computer
sophisticated. Merged cells can appear anywhere. Unless
I am misunderstanding you, adding blank adjacent columns
or rows does not seem to resolve this problem.

 

[Tom Ogilvy]

If you want to count the total cells

=ROWS(C1:C20)

this is not affected by whether the cells are merged or not. But getting
the total occupied would be affected if you are trying to count the number
of cells with a corporate name in the cell.

Regards,
Tom Ogilvy

 

[Guest]

Hi Tom:

The problem is the count remains the same whether or not
some cells are merged. I'm trying to get a true count of
all booths: that is occupied single booths + unoccupied
single booths + occupied merged booths. A merged double
booth or merged triple booth are each counted as 1.

 

[Harlan Grove]

The problem is the count remains the same whether or not
some cells are merged. I'm trying to get a true count of
all booths: that is occupied single booths + unoccupied
single booths + occupied merged booths. A merged double
booth or merged triple booth are each counted as 1.

....

The problem is that there is no simple way to perform calculations on ranges
that include merged cells. They're a feature that, on a superficial level,
appears useful, but when you dig deeper it proves to be far more trouble
than it's worth.

That said, there's a way to do what you want, but it *REQUIRES* using VBA to
write a user-defined function. If using VBA at all or using it to write udfs
is unacceptable, you just can't do what you want to do with merged cells, so
you'll need to stop using them.

Here are two possible udfs.


Function tcount(ParamArray a() As Variant) As Long
Dim x As Variant, y As Variant

Application.Volatile

For Each x In a

If TypeOf x Is Range Then
For Each y In x
If VarType(y.Value) = vbDouble Or VarType(y.Value) = vbDate Then
tcount = tcount + y.MergeArea.Cells.Count
End If
Next y

ElseIf IsArray(x) Then
For Each y In x
If VarType(y) = vbDouble Or VarType(y) = vbDate Then
tcount = tcount + 1
End If
Next y

ElseIf VarType(x) = vbDouble Or VarType(x) = vbDate Then
tcount = tcount + 1

End If

Next x

End Function


Function tcounta(ParamArray a() As Variant) As Long
Dim x As Variant, y As Variant

Application.Volatile

For Each x In a

If TypeOf x Is Range Then
For Each y In x
If Not IsEmpty(y.Value) Then
tcounta = tcounta + y.MergeArea.Cells.Count
End If
Next y

ElseIf IsArray(x) Then
For Each y In x
If Not IsEmpty(y) Then
tcounta = tcounta + 1
End If
Next y

ElseIf Not IsEmpty(x) Then
tcounta = tcounta + 1

End If

Next x

End Function

 

[Daniel D.]

Hi Harlan:

Thanks for your quick and informed reply. I will test
the code Tuesday and let you know my results. Thanks
again for your time and effort.

Dan

 

[Daniel D.]

Hi Harlan:

My previous post to Tom read:

The problem is the count remains the same whether or not
some cells are merged. I'm trying to get a true count of
all booths: that is occupied single booths + unoccupied
single booths + occupied merged booths. A merged double
booth or merged triple booth are each counted as 1.

This post should have read:

The problem is the count remains the same whether or not
some cells are merged. I'm trying to get a true count of
all OCCUPIED booths: that is occupied single booths +
occupied merged booths. A merged double
booth will count as 2, a merged triple booth will count
as 3, etc. I know how many total booths are available
for the show. As the booths get filled by single, double
or triple booth occupancies - I need the total of
occupied booths to determine the percentage of unoccupied
booths still available. I may have for example, 100
exhibitors that occupy 130 booths (since some exhibitors
will use 2, 3, or 4 contiguous booths for their exhibit).
Sorry for the confusion.

NOTE: using counta only counts the number of exhibitors.

Dan

 

[Harlan Grove]

My previous post to Tom read:

The problem is the count remains the same whether or not
some cells are merged. I'm trying to get a true count of
all booths: that is occupied single booths + unoccupied
single booths + occupied merged booths. A merged double
booth or merged triple booth are each counted as 1.

To which Ton replied that you should use ROWS(), which would do this.

This post should have read:

The problem is the count remains the same whether or not
some cells are merged. I'm trying to get a true count of
all OCCUPIED booths: that is occupied single booths +
occupied merged booths. A merged double
booth will count as 2, a merged triple booth will count
as 3, etc. I know how many total booths are available
for the show. As the booths get filled by single, double
or triple booth occupancies - I need the total of
occupied booths to determine the percentage of unoccupied
booths still available. I may have for example, 100
exhibitors that occupy 130 booths (since some exhibitors
will use 2, 3, or 4 contiguous booths for their exhibit).
Sorry for the confusion.

That's what I assumed when I wrote the UDFs I posted in my previous
response. Did you bother to test those UDFs? Unless and until you have
some specific trouble with those UDFs, I'll assume this latest
response from you is so much wasted bandwidth.

NOTE: using counta only counts the number of exhibitors.

What do you mean by this? The built-in COUNTA() worksheet function?

 

[Daniel D.]

1. Harlan - if you cannot be polite, and prefer to be
smug with your "wise-ass" remarks about "wasting
bandwidth" then kindly do everyone here a favor and
don't "contribute."

2. I did test your code and it didn't count properly. I
figured that was my fault, since I described the problem
wrong initially.

3. Since a cell (which counts as an exhibitor's booth)
can have the exhibitors name in it or be blank, it makes
sense to use the "built-in" Counta function. The Counta
function counts a cell if it contains at least 1
character (numeric or text). However, as I have already
said, this only counts the number of exhibitors not the
number of cells the exhibitors use.

Please - only civil people need try to help!

Thank you.

 

[Harlan Grove]

1. Harlan - if you cannot be polite, and prefer to be
smug with your "wise-ass" remarks about "wasting
bandwidth" then kindly do everyone here a favor and
don't "contribute."

The ascerbity of some respondents (maybe just me) is the cost of the free
advice. If you don't like it, find some other way to get free advice.

2. I did test your code and it didn't count properly. I
figured that was my fault, since I described the problem
wrong initially.

....

If I have the following in A1:A10,

A1 unmerged containing "ABC"
A2 blank
A3:A5 merged containing "DEF"
A6 unmerged containing "GHI"
A7:A8 merged containing "JKL"
A9:A10 merged but blank (as a fubar check)

then the formula =tcounta(A1:A10) returns 7, which is the number of cells
containing anything where the number of such cells includes merged cells,
i.e., 1(A1) + 3(A3:A5) + 1(A6) + 2(A7:A8) = 7. The tcounta() is the second
UDF I posted in my initial response.

So what's not working for you? How about providing a sample of cells you're
working with for which my tcounta() UDF gives the wrong result?

BTW, nastiness encourages others to find fault with my answers when they
have faults to be found and highlighted. That no one else has responded
could be taken as a sign that the UDFs in my original response work. Whether
you know how to use them is a different matter.

 

[Guest]

Your comment below is juvenile and narcissistic:

The ascerbity of some respondents (maybe just me) is the

cost of the free >advice. If you don't like it, find some
other way to get free advice.

Your code for tcounta only gives a correct count if the
cells to be merged are blank initially.

If cell A1 has "ABC" in it and the client decides at a
later time to include the next adjacent booth (cell)also,
when you merge cells A1 and B1 the count remains the
same - it doesn't update.

Perhaps you're not as smart as you think Buster!

 

[Harlan Grove]

...
...

Your code for tcounta only gives a correct count if the
cells to be merged are blank initially.

If cell A1 has "ABC" in it and the client decides at a
later time to include the next adjacent booth (cell)also,
when you merge cells A1 and B1 the count remains the
same - it doesn't update.

Perhaps you're not as smart as you think Buster!

Perhaps you could have explained what the problem was earlier, or do details
seldom figure into your thoughts, if you have anything remotely resembling
thoughts?

Merging cells is formatting. Formatting changes don't trigger recalculation on
their own. Just add an Application.Volatile call as the first statement after
the Dim statements in tcounta(). This still won't automatically recalc when
cells are merged (again, formatting changes don't trigger recalculation), but
it'll recalc when anything else causes a standard recalc. Even without this
additional statement, you could update tcounta using a full recalc,
[Alt]+[Ctrl]+[F9].