A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can I Use a Count Function for Text?



 
 
Thread Tools Display Modes
  #21  
Old July 3rd, 2008, 11:31 PM posted to microsoft.public.excel.worksheet.functions
Elvira
external usenet poster
 
Posts: 3
Default I need to count a row with something in it

I have a simmilar problem. I need to count rows with combinations of letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the formula to
exclude the numeric factor.

Please advice

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
T. Valko..... You are the best..... this stuff is sooooo simple if you
know
the basic rules ..... oh yea just like life
Thanks again!!!

"T. Valko" wrote:

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not empty.

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
I have been following this strem but have not seen something that I need
....
I need to count a row (e.g., d61:j61) with anything in it - either a
number
or word .... can one of you please help me???








  #22  
Old July 3rd, 2008, 11:47 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default I need to count a row with something in it

It's not clear what you want. Can you provide a more detailed explanation?


--
Biff
Microsoft Excel MVP


"Elvira" wrote in message
...
I have a simmilar problem. I need to count rows with combinations of
letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the formula to
exclude the numeric factor.

Please advice

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
T. Valko..... You are the best..... this stuff is sooooo simple if you
know
the basic rules ..... oh yea just like life
Thanks again!!!

"T. Valko" wrote:

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not empty.

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
I have been following this strem but have not seen something that I
need
....
I need to count a row (e.g., d61:j61) with anything in it - either a
number
or word .... can one of you please help me???










  #23  
Old July 4th, 2008, 12:32 AM posted to microsoft.public.excel.worksheet.functions
Elvira
external usenet poster
 
Posts: 3
Default I need to count a row with something in it

A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed explanation?


--
Biff
Microsoft Excel MVP


"Elvira" wrote in message
...
I have a simmilar problem. I need to count rows with combinations of
letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the formula to
exclude the numeric factor.

Please advice

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
T. Valko..... You are the best..... this stuff is sooooo simple if you
know
the basic rules ..... oh yea just like life
Thanks again!!!

"T. Valko" wrote:

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not empty.

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
I have been following this strem but have not seen something that I
need
....
I need to count a row (e.g., d61:j61) with anything in it - either a
number
or word .... can one of you please help me???











  #24  
Old July 4th, 2008, 02:03 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default I need to count a row with something in it

As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz")),1,""),"")

And copy down as needed.
Then Sum Column B.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed
explanation?


--
Biff
Microsoft Excel MVP


"Elvira" wrote in message
...
I have a simmilar problem. I need to count rows with combinations of
letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the formula
to
exclude the numeric factor.

Please advice

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
T. Valko..... You are the best..... this stuff is sooooo simple if
you
know
the basic rules ..... oh yea just like life
Thanks again!!!

"T. Valko" wrote:

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not
empty.

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
I have been following this strem but have not seen something that
I
need
....
I need to count a row (e.g., d61:j61) with anything in it -
either a
number
or word .... can one of you please help me???













  #25  
Old July 7th, 2008, 09:01 PM posted to microsoft.public.excel.worksheet.functions
Elvira
external usenet poster
 
Posts: 3
Default I need to count a row with something in it

Thank you IT WORKS!! ; 0)

You’re awesome!!

"RagDyer" wrote:

As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz")),1,""),"")

And copy down as needed.
Then Sum Column B.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed
explanation?


--
Biff
Microsoft Excel MVP


"Elvira" wrote in message
...
I have a simmilar problem. I need to count rows with combinations of
letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the formula
to
exclude the numeric factor.

Please advice

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
T. Valko..... You are the best..... this stuff is sooooo simple if
you
know
the basic rules ..... oh yea just like life
Thanks again!!!

"T. Valko" wrote:

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not
empty.

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
I have been following this strem but have not seen something that
I
need
....
I need to count a row (e.g., d61:j61) with anything in it -
either a
number
or word .... can one of you please help me???














  #26  
Old July 8th, 2008, 12:13 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default I need to count a row with something in it

You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
Thank you IT WORKS!! ; 0)

You're awesome!!

"RagDyer" wrote:

As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz")),1,""),"")

And copy down as needed.
Then Sum Column B.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elvira" wrote in message
...
A100
1B00
10C0
D100
=
400
I need to add the rows the problem that I am running into is that it
does
not recognize the number because of the alpha included in the number
I hope this explains it a little better


"T. Valko" wrote:

It's not clear what you want. Can you provide a more detailed
explanation?


--
Biff
Microsoft Excel MVP


"Elvira" wrote in message
...
I have a simmilar problem. I need to count rows with combinations of
letters
and alpha i.e. 45A111 + 45A222 should equal 90333. I need the
formula
to
exclude the numeric factor.

Please advice

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
T. Valko..... You are the best..... this stuff is sooooo simple
if
you
know
the basic rules ..... oh yea just like life
Thanks again!!!

"T. Valko" wrote:

Maybe this:

=COUNTA(D61:J61)

That will return the count of cells in the range that are not
empty.

--
Biff
Microsoft Excel MVP


"Rick" wrote in message
...
I have been following this strem but have not seen something
that
I
need
....
I need to count a row (e.g., d61:j61) with anything in it -
either a
number
or word .... can one of you please help me???
















  #27  
Old July 9th, 2008, 05:06 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default I need to count a row with something in it

"RagDyer" wrote...
As long as your data has a *single* alpha character, and no spaces,
you could create a "helper" column to extract the numeric data,
and then just total that helper column.

With original data starting in A1,
try this in B1:

=IF(A10,--REPLACE(A1,MIN(SEARCH({"a","b","c","d","e","f",

"g","h","i","j","k","l","m","n","o","p","q","r","s ","t","u",
"v","w","x","y","z"},A1&"abcdefghijklmnopqrstuvwx yz")),1,""),"")

....

Alternatively, if there'd never be more than 6 characters and all
numbers would be positive integers, you could do it with a single
formula.

=SUMPRODUCT(--(MID(rng,1,--ISNUMBER(-MID(rng,1,1)))
&MID(rng,2,--ISNUMBER(-MID(rng,2,1)))
&MID(rng,3,--ISNUMBER(-MID(rng,3,1)))
&MID(rng,4,--ISNUMBER(-MID(rng,4,1)))
&MID(rng,5,--ISNUMBER(-MID(rng,5,1)))
&MID(rng,6,--ISNUMBER(-MID(rng,6,1)))))

where rng is a placeholder for the range in question.
  #28  
Old July 17th, 2008, 12:34 PM posted to microsoft.public.excel.worksheet.functions
kev
external usenet poster
 
Posts: 73
Default Can I Use a Count Function for Text?

Hi,

Can anyone please help, i'm trying to count the total number of occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont seem
to be working - any suggestions
thanks
  #29  
Old July 17th, 2008, 05:17 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Can I Use a Count Function for Text?

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks



  #30  
Old July 18th, 2008, 05:17 AM posted to microsoft.public.excel.worksheet.functions
BeckyD
external usenet poster
 
Posts: 2
Default Can I Use a Count Function for Text?

I am trying to use the count function on text in a totally different way...

I want to put the word DUPE or some type of flag in one column based on
whether or not COUNTIF on a different column is greater than 1

So instead of having to eyeball a column to look for formatted columns
indicating that a conditional formula has been met, i'd like a way to use the
autofilter tool on a different column so that only (and all) rows with
duplicates display .. so i can work with just those.

thanks.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:07 PM.


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