Word table, counting occupied cells

[telephoto]

I have a table in word 2003.

One column has X in some of the cells.

In a row at the foot of the table I need to count the total number of X's in the column.

I know "=COUNTA(C2:C40)" works in Excel, but can anyone translate this to word for me please?

Telephoto

 

[SkipVought]

Hi,

MS Word is a word processor. MS Excel is a spreadsheet. Each has it's strengths, based on its intended purpose.

I'd embed an Excel table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[telephoto]

Hi Skip
yes, that would be a way round it, but this is for a friend - I'm not sure he even has Excel!

Word tables have a calculation facility, this is just one I've never used!

T

 

[Markus4]

Hi telephoto,

I assume that the Word table is long, but static - unlike Excel spreadsheets, where cell contents may change.

Then why not select column C, replace X by 1 and use [tt]{=Sum(ABOVE)}[/tt].

Note: [tt]ABOVE[/tt] is a name for all cells in the column above the cell containing the formula. It is localised, so if you do not use an English Word you will have to look it up in Word help.

HTH.

Markus

 

[telephoto]

Markus

you could be onto something here - I tried it but the count doesn't "jump" empty cells, is there a minor glitch in the formula?

T1

 

[telephoto]

I'm answering my own problem here.

Looking in help it states that to sum the whole column I have to insert zeros in the empty cells
That is going to be seen as aggro - and worse, it's not dynamic, once it counts it doesn't change

Skip's embedded Excel table is starting to look favourite - if my friend's got Excel!

 

[Markus4]

Hi,

I tried it but the count doesn't "jump" empty cells, is there a minor glitch in the formula?

I'm sorry, but in my Word it is working.

At least it was, when I entered the formula. After Telephoto's answer I tried to reconstruct his problem. Then a lot of funny things happened.

Even after closing and re-opening the application Word showed an entry in a dialogue which simply shouldn't be there any more. In short: Word seems to keep a history in some distant scope of its large program.

This seems to apply to the name [tt]ABOVE[/tt] as well. After replacing [tt]ABOVE[/tt] with cell addresses the SUM formula worked alright again.

But alas: if Telephoto has no "static" table then an embedded Excel table is the best solution to his problem.

Markus.

 

[fumei]

Assuming the column is number 3...
Assuming the "x" may be upper OR lower case (therefore using UCase...

Function CellText(strIn As String) As String
   CellText = Left(strIn, Len(strIn) - 2)
End Function


Sub NumberOfX()
Dim aTable As Table
Dim aCell As Cell
Dim var
Dim counter As Long

Set aTable = ActiveDocument.Tables(1)
For var = 1 To aTable.Rows.Count
   If UCase(CellText(aTable.Cell(var, 3).Range.Text)) = "X" Then
      counter = counter + 1
   End If
Next
MsgBox "There are " & counter & " X in Column C (3)."
End Sub

Gives the number of "x" (or "X") in the third column.

The Celltext function gets only the string of the cell (stripping off the end-of-cell marker).

55,687.00 hours down....
82 hours to go

tick tick tick tick tick

 

[fumei]

Ooops. Other things happening. You do not need the aCell object.

Here is a generic function and procedure to get any text from any column fron any table.

Function CellText(strIn As String) As String
   CellText = Left(strIn, Len(strIn) - 2)
End Function
Function NumberOfX(aTable As Table, _
   ColumnNum As Long, _
   aString As String) As Long
Dim var
Dim counter As Long

For var = 1 To aTable.Rows.Count
   If UCase(CellText(aTable.Cell(var, ColumnNum).Range.Text)) = UCase(aString) Then
      counter = counter + 1
   End If
Next
NumberOfX = counter
End Function

Sub GenericStuffInColumn()
MsgBox "There are " & NumberOfX( _
   ActiveDocument.Tables(InputBox("Which table - by number?")), _
   InputBox("Which column - by number?"), _
   InputBox("Text to count?")) & "."
End Sub

Obviously you could adjust it so it does more than return a messagebox with (for example):

"There are 12." It is generic. You can use it to point to, and count, any table (aTable), any column (ColumnNum), for any text (aString).

55,687.00 hours down....
<60 hours to go

tick tick tick tick tick

 

[TonyJollans]

Gerry - you're still here! I missed you last week, I hope all is well with you.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

I was sicker than a dog last week. I am only just today out of bed. Yeah, I am sorry I missed you. I actually thought about still going then realized that....hmmmmm, temp is 103 and I am throwing up. Nah, maybe not. I am sorry I missed it (and you) though.

I am "here" - on-line - for this week then I am finished work and off-line. Plus my mother is dying back in Ottawa, so I am flying out right after I finish my last day here (and you can bet I will be working hard that day...not), to say my goodbye and start taking care of all the stuff that will need to be taken care of.

She is 89 and has had an impressive life, so it is not a horrible thing. It will only be horrible if she suffers.

Then I am back here, and I do not have a clue what I am going to do with myself.

55,687.00 hours down....
<60 hours to go

tick tick tick tick tick

 

[TonyJollans]

Sorry you couldn't make it - there is nothing quite like Summit. When I arrived it was 26 and snowing - that would have cooled you down

Sorry about your Mum - it is something several Word MVPs have gone through, or are going through, of late - we are of an age, I guess. I cared for my Mum after my Dad died four years ago and it's not easy. As you say, though, at 89 and having had a good life, it's not too bad if she doesn't suffer.

We will miss you when you do go - but I should be around for the next week to keep tabs on you till then!!


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[telephoto]

fumei

This looks to be what I am after, I thought there would some way of doing it.

I can't look at it right now, but I assume I put this into a macro with a control button?

If that's the case, this will do the business.

From your countdown I'm guessing you're retiring?, so am I, and it's great - enjoy it.

Thanks

T

 

[dcompto]

Are there numbers (or some other character in addition to X) being used in the column? If the column has only X's and blanks, you could:

Select the column
Search and replace: find X, replace with 1
Use COUNT function: =COUNT(B2:B20)

 

[fumei]

That would still not deal with blanks. AND, you would have to do a replace back. Seems excessive.

telephoto: "I can't look at it right now, but I assume I put this into a macro with a control button?"

It is a procedure like any procedure. You can fire it with a button, a keyboard shortcut, an icon on a toolbar, a Call to it from another procedure...whatever. You are not "putting this into a macro", it IS a macro. It can be adjusted and modified accordingly.

55,687.00 hours down....
<60 hours to go

tick tick tick tick tick

 

[dcompto]

The COUNT function DOES deal with blanks (at least in Word 2010), but I can't remember what other versions on which we have used it (we have a mix of 2003, 2007, and 2010 users). As to the search and replace, if the user doesn't mind having 1's in the column instead of X's, they wouldn't have to change it back. It was just a suggestion as we have had to use this workaround in the past.

 

[fumei]

You are correct. I am wrong.

I prefer to use generic procedures if possible. My procedure can tally anything: 1, X, Long strings of text...whatever.

dcompto
fumei
strongm
fumei
dcompto
dcompto
telephoto
fumei

"Column has 3 dcompto."

It can easily be adjusted to count real numbers if desired, rather than strings. It can easily be adjusted to handle multiple searches.

"Column has 3 dcompto, 3 fumei, 1 strongm, 1 telephoto."

In other words, it is flexible and scalable. Combined with designed structure (tables bookmarked so they can be called by name) you can tally up data from tables in all the files in a folder (for example).

55,687.00 hours down....
<60 hours to go

tick tick tick tick tick