Whats the function to count the total times a word is displayed

[Guest]

Hi ..

Which is the best function to use if I wanted to return the total number of
times a word or number (criteria) is used within a specified number of cells
...?

Example : The product code 'S0459' is used (mentioned) 'X' amount of times
within an entire column (specified cells)

Thanks in advance ...

 

[Ben McBen]

Sounds like a job for DCOUNT - see formula help....

-----Original Message-----
Hi ..

Which is the best function to use if I wanted to return the total number of
times a word or number (criteria) is used within a specified number of cells
...?

Example : The product code 'S0459' is used

(mentioned) 'X' amount of times

 

[Bob Phillips]

=COUNTIF(A1:A1000,"S0459")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Bob gave you this formula:
=COUNTIF(A1:A1000,"S0459")
And this works if there's nothing else in the cell.

If you have other stuff in that same cell:
=COUNTIF(A1:A1000,"*S0459*")

Both of these formulas count the number of cells that match (or contain) S0459.
If a cell can have that value twice, it's only counted once.

If you really want to count the number of times S0459 appears in that range:

=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,"S0459","")))/LEN("S0459")
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And be careful. =Substitute() is case sensitive. It'll match S0459, but not
s0459.

If you want to count S0459 and s0459, then this'll work:
=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(UPPER(A1:A1000),"S0459","")))/LEN("S0459")
(also entered with ctrl-shift-enter)