countif help

C

Clint Wagner

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
 
M

merjet

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*"
Click to expand...

=SUMPRODUCT((A:A>2000)*(B:B="*best*"))

HTH,
Merjet
 
D

Dave

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
Click to expand...

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:A25>2000)*(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:A25>2000)*(B1:B25="*best*"))}
in the edit bar. Excel adds to braces for you.

HTH
Dave
 
W

William

Hi merjet

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

=SUMPRODUCT((A1:A100>2000)*(B1:B100="*best*"))


--
XL2002
Regards

William

[email protected]

| > 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:A>2000)*(B:B="*best*"))
|
| HTH,
| Merjet
|
|
 
D

Dave

That's not helping. Im getting N/A everytime. Cant I use a
Click to expand...
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
 
M

Myrna Larson

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

Clint Wagner

I am using the following formula:
=SUMPRODUCT((B28:B39>2000)*(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....
 
F

Frank Kabel

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

=SUMPRODUCT((B28:B39>2000)*(ISNUMBER(SEARCH("Best",D28:D40))))
 
C

Clint Wagner

I am using the following formula:
=SUMPRODUCT((B28:B39>2000)*(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....
 
C

Clint Wagner

I am using the following formula:
=SUMPRODUCT((B28:B39>2000)*(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....
 
P

Pete

Hi

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

Regards

Pete

| I am using the following formula:
| =SUMPRODUCT((B28:B39>2000)*(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 <[email protected]>
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:A>2000)*(B:B="*best*"))
| >>>
| >>>HTH,
| >>>Merjet
| >>>
| >>>
| >>
| >>That's not helping. Im getting N/A everytime. Cant I use a COUNTIF?
| >
| >
 
C

Clint Wagner

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