Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Dates & Text?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Dates & Text?
Excel2003 ... My Range contains "Date" values & various "Text" values ... I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,058
Default Dates & Text?
=COUNTA(A1:A100)-COUNT(A1:A100)
--
Gary''s Student - gsnu200829


"Ken" wrote:

Excel2003 ... My Range contains "Date" values & various "Text" values ... I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Dates & Text?
One way

=COUNTA(A1:A10)-COUNT(A1:A10)

Mike

"Ken" wrote:

Excel2003 ... My Range contains "Date" values & various "Text" values ... I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 694
Default Dates & Text?
HI Ken
Try this one also works: =SUMPRODUCT(ISTEXT(A10:A19)+0)
HTH
John
"Ken" wrote in message
...
Excel2003 ... My Range contains "Date" values & various "Text" values ...
I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 99
Default Dates & Text?
Hi,

Even shorter formula is just =COUNTIF(A1:A100,"*")

Dave

"Ken" wrote:

Excel2003 ... My Range contains "Date" values & various "Text" values ... I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Dates & Text?
A slight wrinkle here ... I just realized that some of the cells I thought
were BLANK or EMPTY actually contain a single apostrophe ( ' ) ... which this
formula picks up as "Text" ... I need to exclude these ... I could do another
minus (-) countif, but my problem will also compound in that I have 4 non
contiguous ranges. Just kill 1 snake @ a time I guess???

Thanks ... Kha

"Dave Curtis" wrote:

Hi,

Even shorter formula is just =COUNTIF(A1:A100,"*")

Dave

"Ken" wrote:

Excel2003 ... My Range contains "Date" values & various "Text" values ... I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Dates & Text?
Board ... Previously, I may have been writing this incorrectly ... :(

What I want for a returned value is:

The NETWORKDAYS ... between TODAY & 01/01/09 ... excluding any DAYs with
TEXT Codes (that are not WEEKENDS).

Thanks ... Kha




"Ken" wrote:

A slight wrinkle here ... I just realized that some of the cells I thought
were BLANK or EMPTY actually contain a single apostrophe ( ' ) ... which this
formula picks up as "Text" ... I need to exclude these ... I could do another
minus (-) countif, but my problem will also compound in that I have 4 non
contiguous ranges. Just kill 1 snake @ a time I guess???

Thanks ... Kha

"Dave Curtis" wrote:

Hi,

Even shorter formula is just =COUNTIF(A1:A100,"*")

Dave

"Ken" wrote:

Excel2003 ... My Range contains "Date" values & various "Text" values ... I
wish a formula to count the "Text" values only.

01/01/09
01/02/09
H
01/04/09
V
01/06/09
01/07/09
01/08/09
O/S
01/10/09

Formula should return ... 3

Thanks ... Kha
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
How do I convert dates stored as dates to text? diamunds Excel Discussion (Misc queries) 5 September 7th 07 05:38 PM
Format text 'dates' to real dates Jacy Excel Worksheet Functions 4 July 24th 06 02:10 AM
dates and text karene Excel Discussion (Misc queries) 1 November 18th 05 04:21 PM
dates and text Sloth Excel Discussion (Misc queries) 0 November 18th 05 04:16 PM
Text with Dates littlegreenmen1 Excel Discussion (Misc queries) 3 June 15th 05 05:13 PM


All times are GMT +1. The time now is 08:04 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement