Can I Use a Count Function for Text?

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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(A1>0,--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.
 
RagDyer said:
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(A1>0,--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,""),"")
Click to expand...
....

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.
 
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
 
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
 
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.
 
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.
Click to expand...
So, something like this. You'll have to modify the COUNTIF part.
=IF(COUNTIF($C$3:$C$100,C3)>1,"DUPE","")
 
thanks for your help!!

T. Valko said:
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
Click to expand...
 
Hello,
I need to count how many times does a word "ITS" appear in my column. the
problem is that it appears multiple times within the same cell but excel
counts that cell just once instead of lets say 5 times when ITS appears 5
times in the cell. and also, my list is filtered so it looks like excel is
including the missing lines as well which i dont need.

thank you
 
One way, using a helper column.

1) In an unused column (say "H") enter

H1: =IF(SUBTOTAL(103,A1),A1)

and copy down as far as required, say, H1000. Hide the column.

2) In your target cell, array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(H1:H1000),-LEN(SUBSTITUTE(H1:H1000,"its","")))/LEN("its")
 
I have 2 separate columns and I am trying to count the nu,ber of occurances
for a 2 different values. i.e. how many times admin column C and sick Column
D show up. Tried the SUMPRODUCT and it doesn't seem to work. I get a value of
0 when there should be 3.
 
Does your formula look something like this:

=SUMPRODUCT((C1:C100="admin")*(D1:D100="sick"))

If you get a result of 0 then that means there are no matches. There may be
leading/traiking spaces or other unseen characters causing this. For
example:

_admin
admin_
_sick
sick_

Where the underscore represents an unseen character. Check your data and
make the necessary corrections.
 
Thanks Biff,

tried it again and made sure no unseen characters and it seems to work.
Cheers.
 
I just found your discussion group. I also have two columns of information
and when I post the formula it simply appears as an entry. The cell where I
posted the formula is a text cell. What should the cell format be? Sorry!
This is all so new to me.
 
Usually format to General or Number.

THEN ... click back in the formula bar and hit <Enter> to register the
change.
 
Back
Top