Countif & wildcard formula error...

  • Thread starter Thread starter Kevin Lyons
  • Start date Start date
K

Kevin Lyons

Hello,

I have a spreadsheet that I need to count those cells (once) which contain
the word "delta"

Cell A2 contains: delta
Cell B2 contains: =COUNTIF($E$2:$E$20,"*"&A2&"*")

The actual count for that range should be 11, but it is returning only 9.
For example, Cell E6 contains:

Product Overview Over Sweat Bring Positioning New Boxing Football Baseball
Positioning New Features Competitive Ping Pong Features Competitive Order
Cash Heaven e.g. Topics Linux Under Friend Delta Always Do They Have To
Enjoy The Same Pranks Just Leave Me Alone

The above formula counts the occurrence of delta, but Cell E5 contains:

Product Overview Positioning New Features Competitive Order to Cash
FundamentalsProduct Features and Functionality eg ProcessConnect i General
Ledger Management Advanced Financial BudgetingSpecialization Compliance
training Ethics Sexual Harassment Delta product features Incentive
Compensation Functional Overview

For the life of me I cannot figure out why it is not counting it or the
delta in Cell E13.

Additionally, in case there is a problem with the data itself or the
formula, I can arrive at the correct results with either of the following
array formulas:

{=COUNT(SEARCH(A2,E2:E20))}
{=SUM(IF(ISERROR(SEARCH(A2,E2:E20)),0,1))}

However, I really want to understand why the formula in Cell B2 does not
work properly. Does anyone have any thoughts as to figuring out why the
formula isn't working as it should?

Thanks much,

Kevin
 
Kevin ,

It seems to be because the word Delta spans the character position 255 in
the instance that doesn't get counted. In the E15 example, remove just one
space and it gets counted. 255 seems to be a magic number when entering text
in a cell.

This is more complicated but seems to work ok using FIND

=SUMPRODUCT(--(NOT(ISERROR(FIND(LOWER(A2),LOWER($E$2:$E$20))))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
It seems to be because the word Delta spans the character position 255 in
the instance that doesn't get counted. In the E15 example, remove just one
space and it gets counted. 255 seems to be a magic number when entering text
in a cell.
Click to expand...
...

?!

In XL8/97 SR-2, when I enter the following formula in A1

=REPT("#",1020)&"foo@"

and the next formula in A2

=COUNTIF(A1,"*foo*")

the second formula returns 1. However, when I copy A1 and paste special as
values on top of itself, the A2 formula returns 0.

WHAT A WONDERFUL SPREADSHEET!

FWIW, if I then enter the formula

=REPT("#",32000)&"foo@"

the formula in A2 again returns 1. So, bizarre as it may seem, COUNTIF seems to
truncate the values of cells containing text constants but not cells containing
formulas evaluating to text. I can only repeat,

WHAT A WONDERFUL SPREADSHEET!

Kinda makes ya wonder how many Excel models may be fubar because of this bug.
Who wants to pay for the priviledge of calling Miocrosoft to report this bug? Or
has Microsoft officially annointed this a 'feature'?

With regard to the workaround formula,
=SUMPRODUCT(--(NOT(ISERROR(FIND(LOWER(A2),LOWER($E$2:$E$20))))))
Click to expand...

this could be shortened to

=SUMPRODUCT(--ISNUMBER(SEARCH(A2,$E$2:$E$20)))

As a general rule, NOT(ISERROR(FIND(...))), NOT(ISERROR(SEARCH(...))),
NOT(ISERROR(MATCH(...))), NOT(ISERROR(VLOOKUP(...))) and
NOT(ISERROR(HLOOKUP(...))) can and should always be replaced with
ISNUMBER(FIND(...)) etc.
 
Harlan Grove said:
With regard to the workaround formula,


this could be shortened to

=SUMPRODUCT(--ISNUMBER(SEARCH(A2,$E$2:$E$20)))

As a general rule, NOT(ISERROR(FIND(...))), NOT(ISERROR(SEARCH(...))),
NOT(ISERROR(MATCH(...))), NOT(ISERROR(VLOOKUP(...))) and
NOT(ISERROR(HLOOKUP(...))) can and should always be replaced with
ISNUMBER(FIND(...)) etc.
Click to expand...

Then why did your proposed 'improvement' use ISNUMBER(SEARCH(...))?
 
Then why did your proposed 'improvement' use ISNUMBER(SEARCH(...))?
Click to expand...

Did you miss the 'etc.' at the end? Or should I have listed all the more
efficient work-alikes? Well, if I must,

Don't use NOT(ISERROR(FIND(...))), use ISNUMBER(FIND(...)) instead.

Don't use NOT(ISERROR(SEARCH(...))), use ISNUMBER(SEARCH(...)) instead.

Don't use NOT(ISERROR(MATCH(...))), use ISNUMBER(MATCH(...)) instead.

Don't use NOT(ISERROR(VLOOKUP(...))), use ISNUMBER(VLOOKUP(...)) instead.

Don't use NOT(ISERROR(HLOOKUP(...))), use ISNUMBER(HLOOKUP(...)) instead.
 
Back
Top