Countif

[Doug]

Two problems with using Countif statements.

1) In column M there is a list of dates with general
formating. Example: 6/29/04. I have tried using the
following countif function to count the number of times
the current date appears: =COUNTIF(M4:M42,TODAY())

It comes up with 0. The correct response should have been
28.

What's wrong and how do I fix it?

2) Column F contains a list of 3 character text in most of
it's cells, a few are blank. Column O contains another
list of 3 character text, some of it matching text in
column F. These text are abreviations for geograpic area.
I have tried using the following countif function to count
the number of times any of the text in column F appears in
column O:

=COUNTIF(F4:F41,O4:O23)

It comes up with 1, it should have counted 26. When the
cell with the countif function is selected and I click on
the fx button and the function argument window opens; to
the right of the criteria entry box, the only text that is
showing is the text located in O10. O4 through O9 and O11
through O23 are not showing. The text in O10 appears one
time in the F column, which is probally why it comes up
with 1.

Any ideas on what is causing this and how to fix it?

Thanks in advance,
Doug

 

[Biff]

Hi Doug!

Let's take the easy one first!

For your second problem, try this:

=SUM(COUNTIF(F4:F41,O4:O23) entered as an array
CTRL+SHIFT+ENTER

Now, for the date problem .....

Your dates are probably entered as *TEXT*. Try this:
Copy an MT cell. Select the cells that contain
the "dates". Now, Paste Special>Add>OK.

See if that converts the text dates into recognized
numeric dates.

Biff

 

[Biff]

OOOPS!

Hmmm...

"I have tried using the following countif function to
count the number of times any of the text in column F
appears in column O:

=COUNTIF(F4:F41,O4:O23)

It comes up with 1, it should have counted 26."

Question: How can it come up with 26 when there are only
20 cells from O4:O23? Unless I don't understand your
problem???

Biff

 

[Harlan Grove]

"I have tried using the following countif function to
count the number of times any of the text in column F
appears in column O:

=COUNTIF(F4:F41,O4:O23)

It comes up with 1, it should have counted 26."

Question: How can it come up with 26 when there are only
20 cells from O4:O23? Unless I don't understand your
problem???

You're confused about how COUNTIF works. F4:F41 spans 36 cells, so COUNTIF
for any *one* of the cells in O4:O23 could easily return 26. If the OP were
summing the COUNTIF *array* result, the maximum possible return value would
be 720 (36 times 20).

And better to use SUMPRODUCT rather than sum to avoid needing to remember
array entry, so

=SUMPRODUCT(COUNTIF(F4:F41,O4:O23))

 

[Harlan Grove]

...
...

You're confused about how COUNTIF works. F4:F41 spans 36 cells, . . .

...

And I'm flat out wrong. F4:F41 is 38 cells. If only I could count!

 

[Biff]

I must have suffered a major brain malfunction!

For some reason I switched the Countif arguments in my
thought process:

=Countif(Criteria,Range)

In any event, the result of the formula would have been
correct.

Biff

 

[Doug]

Thanks for your help,

On the first problem with the dates I tried copying a
blank cell, then paste special/add/OK into the cells with
the dates. The cell with =COUNTIF(M4:M42,TODAY()) in it
still gives me a 0. Any other ideas to fix this.

The second problem, both functions work like a charm:

=SUM(COUNTIF(F4:F41,O4:O23) entered as an array
CTRL+SHIFT+ENTER
and
=SUMPRODUCT(COUNTIF(F4:F41,O4:O23))
Both give me the correct response.

Aside from not having to enter the SUMPRODUCT function as
an array, what are the differences between using these two
functions. Are there any other advantages or disadvantages
to using one over the other.

Thanks again,
Doug

 

[Biff]

....

On the first problem with the dates I tried copying a
blank cell, then paste special/add/OK into the cells with
the dates. The cell with =COUNTIF(M4:M42,TODAY()) in it
still gives me a 0. Any other ideas to fix this.

....

Are these dates imported from a different source? The
dates are *TEXT* and need to be converted back to numeric
value dates.

Try entering this in a cell somewhere:

=VALUE(M42)

What's the result?

If all else fails, you can just use the text
string "6/29/04" in place of TODAY() in your formula, but
that isn't really desirable.



....

The second problem, both functions work like a charm:

=SUM(COUNTIF(F4:F41,O4:O23) entered as an array
CTRL+SHIFT+ENTER
and
=SUMPRODUCT(COUNTIF(F4:F41,O4:O23))
Both give me the correct response.

Aside from not having to enter the SUMPRODUCT function as
an array, what are the differences between using these two
functions. Are there any other advantages or disadvantages
to using one over the other?

....

The SUMPRODUCT function takes arrays as arguments. In
general, you want to avoid using array formulas if
possible. They take somewhat longer to resolve and can
bloat your spreadsheet if you use a large number of them.