ExcelBanter - countif help

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - countif help (https://www.excelbanter.com/excel-programming/305334-countif-help.html)

Clint Wagner

countif help

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

merjet

countif help

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

Dave[_42_]

countif help

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

William[_2_]

countif help

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
|
|

Clint Wagner

countif help

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

countif help

Dave[_42_]

countif help

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

Clint Wagner

countif help

Clint Wagner

countif help

Myrna Larson

countif help

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?

Clint Wagner

countif help

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?

Frank Kabel

countif help

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?

Clint Wagner

countif help

Clint Wagner

countif help

Frank Kabel

countif help

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?

Pete[_20_]

countif help

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?
|
|

Clint Wagner

countif help

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.

All times are GMT +1. The time now is 12:07 AM.