COUNTIF vs. Edit/Find (Crtl F)

[DavidJ726]

Sheet one is a "calendar" of events and in the various days different
individuals are shown as assigned to a specific task. If I want to see how
many times D.Smith is assigned, I can hit Ctrl F and search for all
occourences of D.Smith, or even just smith, and all occurences will be
dispalyed in the resulting dialogue box.

I'm trying to perform the same task with the =COUNTIF function but can't get
it to work in the following scenerio;

If I put the formula in sheet 2, pointing back to sheet 1, I get 0 results.
=COUNTIF(Sheet1!A1:J76,"D.Smith")

If I use the same function in Sheet 2 where the formula (and test data) is
located, I get an accurate count, but ONLY with an exact match.
=COUNTIF(A1:B4,"D.Smith")

So what am I missing? It appears that COUNTIF does not span across
worksheets, but I doubt that's the case.

And, what can I do to to have a more flexible search? In other words, if I
search for smith and not D.Smith, I want all the D.Smith's to be found, one
thing I'm looking for is if there was inconsistency in the naming (not
incorrect spelling) So I would like any occurence of Smith to be found.

Any help?
Thanks,

David

 

[Dave Peterson]

Do you have calculation set to manual?
(Tools|Options|Calculation tab to check)

And if you want to search for smith when it can be embedded with other
characters:

=COUNTIF(Sheet1!A1:J76,"*Smith*")
or
=COUNTIF(A1:J76,"*Smith*")

I've never seen =countif() fail to work correctly--even across worksheets.

 

[DavidJ726]

Thanks Dave,

I checked and calibration was set to automatic. The asterick worked, and
then I went ahead and locked the cell / range reference with the $. Not
sure what to think but it now works with the exact same formula. I'm gonna
blame MS, it certainly can't be cockpit error

Thanks again.

 

[Dave Peterson]

I sometimes just blame others closer to the problem. It can (should?) never be
the nut behind the wheel!