Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 4th 12, 01:05 AM posted to microsoft.public.excel.newusers
LAS LAS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 3
Default Count number of text rows???

I want to have the number of students in a class to automatically update
when I add or remove a row. In other words, I want the value for "count" to
always be correct. I see how I could do this by making an =row() column,
but that wouldn't be idiot proof. Any new row would have to have that value
added by the user. Is there any way to count rows that have no numeric
value in them?

Example

First Name Last Name
Alma Diderot
Mark Woolser
Amy Fleisher
Count 3



  #2   Report Post  
Old September 4th 12, 07:16 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,685
Default Count number of text rows???

Hi,

Am Mon, 3 Sep 2012 20:05:05 -0400 schrieb LAS:

Example

First Name Last Name
Alma Diderot
Mark Woolser
Amy Fleisher
Count 3


try:
=COUNTA(A:A)-1
-1 for the header


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Old September 4th 12, 07:59 AM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by LAS View Post
I want to have the number of students in a class to automatically update
when I add or remove a row. In other words, I want the value for "count" to
always be correct. I see how I could do this by making an =row() column,
but that wouldn't be idiot proof. Any new row would have to have that value
added by the user. Is there any way to count rows that have no numeric
value in them?

Example

First Name Last Name
Alma Diderot
Mark Woolser
Amy Fleisher
Count 3
Hi,

I'm not sure I'm understanding what you mean. When you say "is there any way to count rows that have no numeric value in them?", do you mean you're trying to count text cells only or a combination of any cells in a given range that do not contain numbers, whether or not they are blank or contain text?

As far as I can see, in your example data above (presuming it covers cells A1 to B5) if you enter =COUNTA(A1:A5)-2 into cell B5 that should give you the result you're looking for and will allow you to enter rows and delete rows.

All it does is count the number of populated cells between A1 and A5 and subtracts 2 from that total to account for the header value in A1 and the footer value in A5.
  #4   Report Post  
Old September 4th 12, 10:49 PM posted to microsoft.public.excel.newusers
LAS LAS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 3
Default Count number of text rows???

Thanks so much!!!
That's not in the list of any of the formulas on the formula tab that I can
see. Should I have been able to find this myself?
I'm using Excel 2010

"Claus Busch" wrote in message ...

Hi,

Am Mon, 3 Sep 2012 20:05:05 -0400 schrieb LAS:

Example

First Name Last Name
Alma Diderot
Mark Woolser
Amy Fleisher
Count 3


try:
=COUNTA(A:A)-1
-1 for the header


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

  #5   Report Post  
Old September 5th 12, 06:49 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,685
Default Count number of text rows???

Hi,

Am Tue, 4 Sep 2012 17:49:35 -0400 schrieb LAS:

That's not in the list of any of the formulas on the formula tab that I can
see. Should I have been able to find this myself?
I'm using Excel 2010


Formulas = Insert Function = Statistical


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count number of rows with specific text Rock Excel Worksheet Functions 2 May 24th 10 08:46 PM
count of number of rows within a range with a certain text cooey Excel Worksheet Functions 5 May 13th 10 08:18 PM
count number of rows with 2 matching text cells smcmoran Excel Worksheet Functions 5 September 29th 08 05:53 PM
Count number of rows, where non relevant rows are hidden Pieter Excel Discussion (Misc queries) 2 November 8th 06 12:24 PM
Count rows and insert number to count them. Mex Excel Discussion (Misc queries) 6 August 23rd 06 02:29 AM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017