Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2016, 10:40 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default 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
The count seems to depend on how many cells are blank, but it is more complicated than that.



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.
Reply With Quote
  #2  
Old 08-22-2016, 11:18 PM
macropod's Avatar
macropod macropod is offline Getting the Count formula to count all rows Windows 7 64bit Getting the Count formula to count all rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 08-23-2016, 12:03 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

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?
Attached Files
File Type: docx Count Field.docx (29.7 KB, 11 views)
Reply With Quote
  #4  
Old 08-23-2016, 12:26 AM
macropod's Avatar
macropod macropod is offline Getting the Count formula to count all rows Windows 7 64bit Getting the Count formula to count all rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Jennifer Murphy View Post
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.
That's as expected - empty cells are counted.
Quote:
Originally Posted by Jennifer Murphy View Post
It appears that you are correct about numbers, but not about text
Your table1 A3 confirms what I said - cells containing only text are not counted - they are treated as empty. By way of clarification, as soon as one of the cells to be counted contains a number, any non-numeric cells are ignored. It's more complicated than that too - any blanks above a numeric cell will cause the 'ABOVE' argument to function incorrectly. Try, for example, inserting numbers into cells D2, D5 & D7 of table A1, then updating the formula in D8.
Quote:
Originally Posted by Jennifer Murphy View Post
This result makes no sense to me. What possible use could this have?
The ways of MS are sometimes inscrutable...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-23-2016, 05:02 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The ways of MS are sometimes inscrutable...
More like incoherent!

At least now I know how to get what I need.

Thanks for the help.
Reply With Quote
  #6  
Old 08-23-2016, 06:19 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Here's a more complete example document. I think this covers all of the variations. What a can of worms.
Attached Files
File Type: docx Count Field (short).docx (30.3 KB, 6 views)
Reply With Quote
  #7  
Old 08-23-2016, 08:07 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

I was wrong. I found a couple more quirks. Here's a more complete summary.
Attached Files
File Type: docx Count Field (short).docx (32.8 KB, 9 views)
Reply With Quote
  #8  
Old 08-23-2016, 02:57 PM
macropod's Avatar
macropod macropod is offline Getting the Count formula to count all rows Windows 7 64bit Getting the Count formula to count all rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #9  
Old 08-23-2016, 06:44 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

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.
Attached Files
File Type: docx Count Field (error).docx (30.7 KB, 7 views)
Reply With Quote
  #10  
Old 08-23-2016, 07:57 PM
macropod's Avatar
macropod macropod is offline Getting the Count formula to count all rows Windows 7 64bit Getting the Count formula to count all rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #11  
Old 08-23-2016, 09:26 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Getting the Count formula to count all rows Windows XP Getting the Count formula to count all rows Office 2007
Competent Performer
Getting the Count formula to count all rows
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Wow! That works. But why doesn't the formulas I have work when there is only 1 row? Why? Why? Why? It's driving me nuts.
Reply With Quote
  #12  
Old 08-23-2016, 09:37 PM
macropod's Avatar
macropod macropod is offline Getting the Count formula to count all rows Windows 7 64bit Getting the Count formula to count all rows Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

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
Getting the Count formula to count all rows 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:55 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft