I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"

Thanks,
Rog

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"

=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet

Clint Wagner wrote in news:#Ti$JEMdEHA.2908
@TK2MSFTNGP10.phx.gbl:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the
text in
column B ="*best*"

Thanks,
Rog


If I understand your needs correctly as counting only the number of
intersect points between column A and column B this should work for
you.

Enter this as the formula
=SUM((A1:A252000)*(B1:B25="best"))
adjusting A25 and B25 to suit your needs then press
CTRL+SHIFT+ENTER simultaneously to establish it as an array
formula.

You will know you have correctly entered it as an array formula
when you see
{=SUM((A1:A252000)*(B1:B25="*best*"))}
in the edit bar. Excel adds to braces for you.

HTH
Dave

Hi merjet

AFAIK SUMPRODUCT does not work on entire columns - need something like.....

=SUMPRODUCT((A1:A1002000)*(B1:B100="*best*"))


--
XL2002
Regards

William



"merjet" wrote in message
...
| I need help with a countif that will:
| Count the number of times that: data in column A 2000 and the text in
| column B ="*best*"
|
| =SUMPRODUCT((A:A2000)*(B:B="*best*"))
|
| HTH,
| Merjet
|
|

merjet wrote:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet


That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

merjet wrote:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet


That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

Clint Wagner wrote in news:4107E174.3000403
@indiana.edu:

merjet wrote:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the
text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet


That's not helping. Im getting N/A everytime. Cant I use a
COUNTIF?

If you adjust Merjet's values of A:A and B:B to reflect your range
(A1:A500 and B1:B500) it will work.

Dave

merjet wrote:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet


That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

merjet wrote:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet


That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

COUNTIF supports only one criterion. You have 2, so no, you can't use COUNTIF
here. You need SUMPRODUCT or an array formula.

On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner wrote:

merjet wrote:

I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet


That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

I am using the following formula:
=SUMPRODUCT((B28:B392000)*(D28:D40="*Best*"))
and it gives me #N/A

Is there something wrong with this? I have #'s that range from 2-4000
in column B and Words that do contain "Best"

Any help? I will email file if it helps....

Myrna Larson wrote:

COUNTIF supports only one criterion. You have 2, so no, you can't use COUNTIF
here. You need SUMPRODUCT or an array formula.

On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner wrote:


merjet wrote:


I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet



That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

Hi
SUMPRODUCT does not support this kind of wildcards. Try instead

=SUMPRODUCT((B28:B392000)*(ISNUMBER(SEARCH("Best" ,D28:D40))))


--
Regards
Frank Kabel
Frankfurt, Germany


Clint Wagner wrote:
I am using the following formula:
=SUMPRODUCT((B28:B392000)*(D28:D40="*Best*"))
and it gives me #N/A

Is there something wrong with this? I have #'s that range from
2-4000
in column B and Words that do contain "Best"

Any help? I will email file if it helps....

Myrna Larson wrote:

COUNTIF supports only one criterion. You have 2, so no, you can't
use COUNTIF here. You need SUMPRODUCT or an array formula.

On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner
wrote:


merjet wrote:


I need help with a countif that will:
Count the number of times that: data in column A 2000 and the
text in column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet



That's not helping. Im getting N/A everytime. Cant I use a
COUNTIF?

I am using the following formula:
=SUMPRODUCT((B28:B392000)*(D28:D40="*Best*"))
and it gives me #N/A

Is there something wrong with this? I have #'s that range from 2-4000
in column B and Words that do contain "Best"

Any help? I will email file if it helps....

Myrna Larson wrote:

COUNTIF supports only one criterion. You have 2, so no, you can't use COUNTIF
here. You need SUMPRODUCT or an array formula.

On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner wrote:


merjet wrote:


I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet



That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

I am using the following formula:
=SUMPRODUCT((B28:B392000)*(D28:D40="*Best*"))
and it gives me #N/A

Is there something wrong with this? I have #'s that range from 2-4000
in column B and Words that do contain "Best"

Any help? I will email file if it helps....

Myrna Larson wrote:

COUNTIF supports only one criterion. You have 2, so no, you can't use COUNTIF
here. You need SUMPRODUCT or an array formula.

On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner wrote:


merjet wrote:


I need help with a countif that will:
Count the number of times that: data in column A 2000 and the text in
column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet



That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?

Hi
see your other resposne. Please try to avoid this kind of multiposting!

--
Regards
Frank Kabel
Frankfurt, Germany


Clint Wagner wrote:
I am using the following formula:
=SUMPRODUCT((B28:B392000)*(D28:D40="*Best*"))
and it gives me #N/A

Is there something wrong with this? I have #'s that range from
2-4000
in column B and Words that do contain "Best"

Any help? I will email file if it helps....

Myrna Larson wrote:

COUNTIF supports only one criterion. You have 2, so no, you can't
use COUNTIF here. You need SUMPRODUCT or an array formula.

On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner
wrote:


merjet wrote:


I need help with a countif that will:
Count the number of times that: data in column A 2000 and the
text in column B ="*best*"


=SUMPRODUCT((A:A2000)*(B:B="*best*"))

HTH,
Merjet



That's not helping. Im getting N/A everytime. Cant I use a
COUNTIF?

Hi

Replace D28:D40 with D28:D39 so that the range in Column B is matched.
--

Regards

Pete

"Clint Wagner" wrote in message
...
| I am using the following formula:
| =SUMPRODUCT((B28:B392000)*(D28:D40="*Best*"))
| and it gives me #N/A
|
| Is there something wrong with this? I have #'s that range from 2-4000
| in column B and Words that do contain "Best"
|
| Any help? I will email file if it helps....
|
| Myrna Larson wrote:
|
| COUNTIF supports only one criterion. You have 2, so no, you can't use
COUNTIF
| here. You need SUMPRODUCT or an array formula.
|
| On Wed, 28 Jul 2004 13:30:52 -0400, Clint Wagner
wrote:
|
|
| merjet wrote:
|
|
| I need help with a countif that will:
| Count the number of times that: data in column A 2000 and the text
in
| column B ="*best*"
|
|
| =SUMPRODUCT((A:A2000)*(B:B="*best*"))
|
| HTH,
| Merjet
|
|
|
| That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?
|
|

Pete,
That worked!! I have adjusted all of my formulas. Thanks for the help
everyone!

Pete wrote:
Hi

Replace D28:D40 with D28:D39 so that the range in Column B is matched.