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

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.

--
Regards,
Tom Ogilvy

"Daniel D." <(E-Mail Removed)> wrote in message
news:10abe01c3f43d$0f2aa020$(E-Mail Removed)...
> 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

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.

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

<(E-Mail Removed)> wrote in message
news:1058001c3f441$d7f9a6a0$(E-Mail Removed)...
> 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.

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.


>-----Original Message-----
>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
>
><(E-Mail Removed)> wrote in message
>news:1058001c3f441$d7f9a6a0$(E-Mail Removed)...
>> 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.
>
>
>.
>

<(E-Mail Removed)> wrote...
>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

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

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

"Daniel D." <(E-Mail Removed)> wrote...
>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?

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.