#1
|
||||
|
||||
Getting the Count formula to count all rows
I have a table with 7-8 columns. I would like to place a field at the bottom of one of the columns that will tell me how many rows there are in the table. The problem I am having is that it does not seem to count rows with empty cells.
In this table, the top row is set as the header. The three middle rows contain data. The bottom row in my real table contains totals and averages. In this example, I've filled each cell in the bottom row with the formula: "{=count(above)-1}. Code:
A B C D E F G H 1 2 2 3 1 2 2 3 1 2 2 3 3 2 1 0 2 0 1 2 I would like all of the formulas to return "3", the number of rows in the table regardless of contents, excluding the header row. How can I get it to do that? I do not want to add another row with a sequence number or a numbered list. |
#2
|
||||
|
||||
There is no Word field formula to do that for columns containing a mix of numbered cells with empty cells or cells containing text instead of numbers. If you have at least one fully-populated or one completely empty column (insofar as numbers are concerned in either case), you could use a formula like:
{=MAX(COUNT(ABOVE)-1,COUNT(LEFT),COUNT(RIGHT))} Another workaround might be to insert an empty column (which you can come close to hiding) and count it's rows.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
I thought I had gotten this to work once before. Checking around, I found another document with a Count formula at the bottom of a column that had empty cells and it still worked. I've attach a simplified version of that document.
It appears that you are correct about numbers, but not about text. This result makes no sense to me. What possible use could this have? |
#4
|
||||
|
||||
Quote:
Quote:
The ways of MS are sometimes inscrutable...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
More like incoherent!
At least now I know how to get what I need. Thanks for the help. |
#6
|
||||
|
||||
Here's a more complete example document. I think this covers all of the variations. What a can of worms.
|
#7
|
||||
|
||||
I was wrong. I found a couple more quirks. Here's a more complete summary.
|
#8
|
||||
|
||||
The vagaries of Word's ABOVE, BELOW, LEFT & RIGHT functions are documented in my Microsoft Word Field Maths Tutorial:
https://www.msofficeforums.com/word/...-tutorial.html Long story short: If you want to get a reliable count of cells in a column or row, make sure the range contains: nothing or text only; or numbers (with or without text), in all cells.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
||||
|
||||
Well, I thought I had it figured out. But when I tried moving the formulas to the top and counting "below", it fails when there is just 1 row.
See attached doc. |
#10
|
||||
|
||||
FWIW, if you have another column filled with numeric data (e.g. your column D), you could use:
{=COUNT(D : D)-1} with or without the spaces either side of the : I included them in my post because otherwise the forum software messes with the formula. Note: the -1 subtracts the count of the Totals row.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
||||
|
||||
Quote:
|
#12
|
||||
|
||||
Who knows?!! I try to avoid Word's ABOVE, BELOW, LEFT & RIGHT functions precisely because of their inconsistent behaviour.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create a formula to count all occurances of HH but multiply by 0.5 first | stupidgirl | Excel | 6 | 06-14-2016 03:50 PM |
simple way to count rows in a table? | bkcell | Word | 4 | 03-09-2015 09:06 PM |
Word Count Formula | clau_dos | Word VBA | 1 | 08-27-2013 02:05 PM |
Count rows and add blank rows accordingly | Hoochtheseal | Word VBA | 1 | 01-29-2013 09:23 PM |
Count If Formula | beb1227 | Excel | 10 | 06-19-2011 11:19 AM |