Can I Use a Count Function for Text?

[Elvira]

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]

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

 

[Elvira]

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

 

[RagDyer]

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.

 

[Elvira]

Thank you IT WORKS!! ; 0)

You’re awesome!!

 

[RagDyer]

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

 

[Harlan Grove]

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,""),"")

....

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.

 

[kEV]

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

 

[T. Valko]

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

 

[beckyd]

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.

 

[Spiky]

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.

So, something like this. You'll have to modify the COUNTIF part.
=IF(COUNTIF($C$3:$C$100,C3)>1,"DUPE","")

 

[kEV]

thanks for your help!!

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

thanks for your help!!

 

[Sedmikraska]

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

 

[JE McGimpsey]

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")

 

[Steve Scatt]

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.

 

[T. Valko]

Does your formula look something like this:

=SUMPRODUCT((C1:C100="admin")*(D1100="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.

 

[Steve Scatt]

Thanks Biff,

tried it again and made sure no unseen characters and it seems to work.
Cheers.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[RagDyer]

Usually format to General or Number.

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