Excel 2000 Using COUNTIF statment with OR statement

[Tom]

Hello,

I'm hopeful someone can help....

I need to count how many occurrences there are of 2 different "words"
in a particular column. For instance I need to count how many times
the word "Pennsylvania" or "PA" are listed in Column 1 (see below).
Answer of course being 4. I believe I can do this with COUNTIF?

Column 1

Pennsylvania
Maryland
MD
Pennsylvania
New Jersey
New York
PA
NY
IL
Delaware
PA


Thanks!

 

[Frank Kabel]

Hi
one way
=SUMPRODUCT(--(A1:A100={"Pennsylvania","PA"}))

 

[hgrove]

Tom wrote...
...

I need to count how many occurrences there are of 2 different
"words" in a particular column. For instance I need to count how
many times the word "Pennsylvania" or "PA" are listed in
Column 1 (see below).

...

COUNTIF accepts only simple criteria, but you could feed it an array o
criteria. So either use two COUNTIF calls or sum the result of COUNTI
called with array criteria.

=COUNTIF(YourList,"Pennsylvania")+COUNTIF(YourList,"PA")

=SUMPRODUCT(COUNTIF(YourList,{"Pennsylvania","PA"})

 

[Frank Kabel]

[....]

=COUNTIF(YourList,"Pennsylvania")+COUNTIF(YourList,"PA")

=SUMPRODUCT(COUNTIF(YourList,{"Pennsylvania","PA"}))

Hi Harlan
just curious: wouldn't the formula
=SUMPRODUCT(--(YourList="Pennsylvania","PA"}))

be more efficient instead of your second solution? (saving one function
call). Or is there a benefit of using COUNTIF inside of SUMPRODUCT in
this case?

Frank

 

[hgrove]

Frank Kabel wrote...
...

just curious: wouldn't the formula
=SUMPRODUCT(--(YourList="Pennsylvania","PA"}))

be more efficient instead of your second solution? (saving one
function call). Or is there a benefit of using COUNTIF inside of
SUMPRODUCT in this case?

Yes, your formula would be more efficient. I was stuck in a menta
trench. However, SUMPRODUCT(COUNTIF(X,{Y,Z})) is more general since
could be a nontrivial 2D range

 

[Aladin Akyurek]

Substitute ISNUMBER/MATCH for COUNTIF. The idiom will be much faster than
the idiom ={...} where {...} of size 3 or more.

 

[hgrove]

Aladin Akyurek wrote...

Substitute ISNUMBER/MATCH for COUNTIF. The idiom will be
much faster than the idiom ={...} where {...} of size 3 or more.

A little cryptic, ={...}. I'd guess you mean

=SUMPRODUCT(--ISNUMBER(MATCH(X,{Y,Z},0)))

If not, how about being more explicit?

. . . However, SUMPRODUCT(COUNTIF(X,{Y,Z})) is more general
since X could be a nontrivial 2D range.

We could quibble about 'much faster'. If most entries in X matched Y
then using MATCH, which returns once it finds the first match, would b
a constant factor faster, but that'd be offset by the overhead of th
ISNUMBER call and the two unary minus ops. On the other hand, if mos
entries in X matched Z, it's not obvious at all tha
ISNUMBER(MATCH(...)) would be faster, and I'd figure that if mos
entries in X matched neither Y nor Z that the COUNTIF formula would b
faster (almost same number of compares, O(N) fewer function calls an
arithmetic ops). Unless COUNTIF is systematically slower tha
ISNUMBER(MATCH(...))

 

[Tom]

Hello,

I'm hopeful someone can help....

I need to count how many occurrences there are of 2 different "words"
in a particular column. For instance I need to count how many times
the word "Pennsylvania" or "PA" are listed in Column 1 (see below).
Answer of course being 4. I believe I can do this with COUNTIF?

Column 1

Pennsylvania
Maryland
MD
Pennsylvania
New Jersey
New York
PA
NY
IL
Delaware
PA


Thanks!

Thanks Guys! Boths methods worked.